Formatting Chart Data Shortcuts Using VBA

Question

I am developing a panel in which there will be many diagrams, and as the data displayed on these diagrams changes, so will the number format. At that moment, when I am right now, I had a problem trying to get the required code format from the spreadsheet on which the data is based, in the middle of the cycle for all series of the diagram. Here's the code for now:

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

, , , . . : , , , , "int", , "#, #" "%" . . , :

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

, : . ! :

With Sheets("CxC").Range("K22:K180")
seriesfmt = .Find(seriesname).Offset(0, -1).Value
End With

, , "" . , With, Set , Matchheet Match, . !

Answer 1

Formula.

:

=SERIES(,,sheetname!sheetRange,)

"3- ", ","

:

Sub FixLabels(whichchart As String)
    Dim cht As Chart
    Dim i As Long

    With Sheets("Dashboard").ChartObjects(whichchart).Chart '<--| reference your chart
        For i = 1 To .SeriesCollection.Count '<--| loop through all series
            With .SeriesCollection(i) '<--| reference current series
                If .Name = "#N/D" Then
                    .DataLabels.ShowValue = False
                Else
                    .HasDataLabels = True '<--| be sure labels are "activated"
                    .DataLabels.ShowValue = True '<--| show data labels
                    .DataLabels.NumberFormat = GetFormat(Split(.Formula, ",")(2)) '<-- set data label format
                End If
            End With
        Next i
    End With
End Sub


Function GetFormat(dataSource As Variant) As String
    With Range(dataSource).Cells(1, 1) '<-- reference the first cell of the data source
        Select Case True
            Case InStr(.Text, "%") > 0
                GetFormat = "#.0%"
            Case Int(CDbl(.Text)) = CDbl(.Text)
                GetFormat = "#"
            Case Else
                GetFormat = "#,###"
        End Select
    End With
End Function