S
smartin
Today I tried to discover how to update the SeriesCollection in several
charts. I would like to loop through my charts and update the existing
SeriesCollection based on some information about the worksheet name.
I started by recording a macro while I updated the x-axis range on one
chart's series, and found this
EXHIBIT A:
ActiveChart.SeriesCollection(1).XValues = "=MI!R32C2:R50C3"
OK, cool. To know the existing XValues for a series all I need to do is
name the chart, point at the correct series index, and viola, right? So,
I assumed I could expose the existing SeriesCollection like this:
Public Sub FixSeriesRanges()
Dim MyChart As Chart
Dim j As Long
For Each MyChart In ThisWorkbook.Charts
For j = 1 To 6
' FAILS (13 - Type Mismatch)
Debug.Print MyChart.SeriesCollection(j).XValues
Next j
Next
End Sub
Not so! It turns out the XValues property is a collection of values that
specify each x-axis value.
So, how can I determine the range of x-axis values, such as returned in
EXHIBIT A?
charts. I would like to loop through my charts and update the existing
SeriesCollection based on some information about the worksheet name.
I started by recording a macro while I updated the x-axis range on one
chart's series, and found this
EXHIBIT A:
ActiveChart.SeriesCollection(1).XValues = "=MI!R32C2:R50C3"
OK, cool. To know the existing XValues for a series all I need to do is
name the chart, point at the correct series index, and viola, right? So,
I assumed I could expose the existing SeriesCollection like this:
Public Sub FixSeriesRanges()
Dim MyChart As Chart
Dim j As Long
For Each MyChart In ThisWorkbook.Charts
For j = 1 To 6
' FAILS (13 - Type Mismatch)
Debug.Print MyChart.SeriesCollection(j).XValues
Next j
Next
End Sub
Not so! It turns out the XValues property is a collection of values that
specify each x-axis value.
So, how can I determine the range of x-axis values, such as returned in
EXHIBIT A?