title: Formatting chart data labels with VBA
body: <p>I am developing a dashboard that will have lots of charts, and as the data shown on these charts change, so will the format of the numbers. At the point I am right now, I've run into a problem trying to retrieve the intended format code from the spreadsheet where the data is based on, in the midst of looping through all the series in the chart.
  Here's the code so far:</p>
  
  <pre><code>Sub FixLabels(whichchart As String)
  Dim cht As Chart
  Dim i, z As Variant
  Dim seriesname, seriesfmt As String
  Dim seriesrng As Range
  
  Set cht = Sheets("Dashboard").ChartObjects(whichchart).Chart
  
      For i = 1 To cht.SeriesCollection.Count
          If cht.SeriesCollection(i).name = "#N/D" Then
              cht.SeriesCollection(i).DataLabels.ShowValue = False
              Else
              cht.SeriesCollection(i).DataLabels.ShowValue = True
                  seriesname = cht.SeriesCollection(i).name
                  Debug.Print seriesname
  </code></pre>
  
  <p>With this I am able to retrieve the name of de series that do not result in error, and hide the series that do. So far so good. 
  Now on to the formatting: There's a column where all the possible series names for this workbook are stored, and one column to the left, there are my formatting codes, which are "int", for integer numbers, "#,#" for numbers with important decimal cases, and "%" for percent rates. These are stored as plain text. So the last bit of the code would look like:</p>
  
  <pre><code>Select Case seriesfmt
     Case "int"
        Cht.SeriesCollection(i).DataLabels.NumberFormat = "#"
     Case "#,#"
        Cht.SeriesCollection(i).DataLabels.NumberFormat = "#,###"
     Case "%"
        Cht.SeriesCollection(i).DataLabels.NumberFormat = "#.0%"
  End Select
  End If
  Next i
  </code></pre>
  
  <p>Finally the real problem here: what goes in between. I cannot retrieve the series format! My best guess was:</p>
  
  <pre><code>With Sheets("CxC").Range("K22:K180")
  seriesfmt = .Find(seriesname).Offset(0, -1).Value
  End With
  </code></pre>
  
  <p>I got errors, telling me the With block was not defined. I tried several combinations of the same command, with or without the With method, with and without the Set method, I tried WorksheetFunction Match, to no avail. Any help solving this issue is greatly apreciated!</p>