SeriesCollection # for Category x axis label

[StanSz]

I have one chart on a worksheet and I want to use VBA to adjust the values of the Series which are in the chart.

There are four series shown in the Source Data info box for the chart. In addition there is a Category X axis label shown which has values also.

I know that I can use, for example, the VBA SeriesCollection(3).Values = "='Sheet1'!$AQ$3:$AQ$9" to specify the values I want.

My question is how to determine the SeriesCollection NUMBER of each of the series. I'm guessing that SeriesCollection(1) refers to the first of the four series shown, and SeriesCollection(2) to the second, etc. etc.

So, does that mean that the Values shown for the Category x axis label would be SeriesCollection(5) ??

Or, does the SeriesCollection numbering BEGIN with the Category x axis and thus the first of the four Series would be SeriesCollection(2) ??

Thanks

Stan

 

[Domenic]

Each series within a chart has XValues and YValues. Simply reference the XValues from one of the series (ie. the first series). So, for example, you could assign the XValues to a Variant variable, and then loop through each element within the array like this...

Sub test()

    Dim vXVals As Variant
    Dim i As Long
    
    vXVals = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues
    
    For i = LBound(vXVals) To UBound(vXVals)
        Debug.Print vXVals(i)
    Next i
    
End Sub

Hope this helps!

 

[StanSz]

Each series within a chart has XValues and YValues. Simply reference the XValues from one of the series (ie. the first series). So, for example, you could assign the XValues to a Variant variable, and then loop through each element within the array like this...

Sub test()

    Dim vXVals As Variant
    Dim i As Long
    
    vXVals = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues
    
    For i = LBound(vXVals) To UBound(vXVals)
        Debug.Print vXVals(i)
    Next i
    
End Sub

Hope this helps!

Dominic,

Your answer about each series having both XValues and YValues did help. I recorded a Macro changing the values of the four Series I have, and I saw exactly what you referenced -- XValues and YValues being set for each series. I'm adjusting my code accordingly.

Thank you.