iterate through chart series collection

[Robert H]

sorry to be bugging the group so mych about charts but Im getting my
butt kicked. The new problem is this:

Building a column chart and needing to add the series x axis labels
which are set in VBA with
..SeriesCollection(#).XValues for each series. I need to set them all
to a named range(the same range of cells)
Im working with: some code removed for clearity)

Dim aChart As Chart
Dim shtNm As String
shtNm = ActiveSheet.Name

Set aChart = Charts.Add
Set aChart = aChart.Location(Where:=xlLocationAsObject,
Name:=shtNm)

Dim xSer As SeriesCollection
For Each xSer In aChart
.XValues = Range("code")
Next

I can set each series individuly but I need to set them as one because
the number of series is variable and can change each time the macro
runs. The named range "CODE" is always the correct size to match the
number in the series.

is there a way to make this work?
Thanks
Robert

 

[Jon Peltier]

You need to declare your variable As Series, not As SeriesCollection, and
loop through each series in the chart's SeriesCollection:

Dim xSer As Series
For Each xSer In aChart.SeriesCollection

- Jon

 

[Vergel Adriano]

I believe you can account for the number of series being dynamic by doing
something like this:

Dim xSer As SeriesCollection
Dim i As Integer
For Each xSer In aChart
For i = 1 To xSer.Count
xSer.Item(i).XValues = Range("code")
Next i
Next

 

[Robert H]

Jon I modified my code as such:

Dim xSer As Series
For Each xSer In aChart.SeriesCollection
xSer.XValues = Range("code")
Next

this runs without error but the instead of each column having the
corresponding "code" underneath it as a label, only the first label is
present, centered under the chart.

the Code named range is: A!$A$2:$A$8 and contains:

Module 01
Module 02
Module 03
Module 14
Module 15
Module 28
Module 34

The resulting Category (X) axis Labels value is =A!$A$2:$A$8 for each
series.

 

[Robert H]

Thanks Vergel.

at this line: For i = 1 To xSer.Count
I get "object doesn't support this property or method

 

[Robert H]

Sorry, the error occurs at the line:

"For Each xSer In aChart" not at the count line

 

[Robert H]

Ive tried a few variations and have the same output on the chart but
get some interesting results watching the code

In the following the actual range is inserted in the series formula
and each series sees the full xvalue range not just its corresponding
value. As previously described, only the first label is
present, centered under the chart.

Dim xSer As Object
For Each xSer In aChart.SeriesCollection
Debug.Print "befor " & xSer.Formula
xSer.XValues = Range("code")
Debug.Print "After " & xSer.Formula
Next

output from DebugPrint
befor =SERIES(A!$A$2,,A!$G$2,1)
After =SERIES(A!$A$2,A!$A$2:$A$8,A!$G$2,1)
befor =SERIES(A!$A$3,,A!$G$3,2)
After =SERIES(A!$A$3,A!$A$2:$A$8,A!$G$3,2)
befor =SERIES(A!$A$4,,A!$G$4,3)
After =SERIES(A!$A$4,A!$A$2:$A$8,A!$G$4,3)
befor =SERIES(A!$A$5,,A!$G$5,4)
After =SERIES(A!$A$5,A!$A$2:$A$8,A!$G$5,4)
befor =SERIES(A!$A$6,,A!$G$6,5)
After =SERIES(A!$A$6,A!$A$2:$A$8,A!$G$6,5)
befor =SERIES(A!$A$7,,A!$G$7,6)
After =SERIES(A!$A$7,A!$A$2:$A$8,A!$G$7,6)
befor =SERIES(A!$A$8,,A!$G$8,7)
After =SERIES(A!$A$8,A!$A$2:$A$8,A!$G$8,7)

The next option was to use the xSer.Name instead of the CODE range
because I noticed that the values were the same. as you can see the
"after" - debug clearly shows a different value in the xvalue portion
of the formula for each series. It just so happens, these are the
correct values. Although I would prefer it the be individual ranges
rather than the values I could use this for the moment. however, when
I looked at the chart, I had the same results!!!!!!!!! only the first
label is present, centered under the chart. The resulting Category
(X) axis Label values are all {"Module 01"} even thought the xSer
formula leads me to think they should be different.

Dim xSer As Object
For Each xSer In aChart.SeriesCollection
Debug.Print "befor " & xSer.Formula
xSer.XValues = xSer.Name
Debug.Print "After " & xSer.Formula
Next

befor =SERIES(A!$A$2,,A!$G$2,1)
After =SERIES(A!$A$2,{"Module 01"},A!$G$2,1)
befor =SERIES(A!$A$3,,A!$G$3,2)
After =SERIES(A!$A$3,{"Module 02"},A!$G$3,2)
befor =SERIES(A!$A$4,,A!$G$4,3)
After =SERIES(A!$A$4,{"Module 03"},A!$G$4,3)
befor =SERIES(A!$A$5,,A!$G$5,4)
After =SERIES(A!$A$5,{"Module 14"},A!$G$5,4)
befor =SERIES(A!$A$6,,A!$G$6,5)
After =SERIES(A!$A$6,{"Module 15"},A!$G$6,5)
befor =SERIES(A!$A$7,,A!$G$7,6)
After =SERIES(A!$A$7,{"Module 28"},A!$G$7,6)
befor =SERIES(A!$A$8,,A!$G$8,7)
After =SERIES(A!$A$8,{"Module 34"},A!$G$8,7)

Stumped again!

 

[Jon Peltier]

No mystery:

=SERIES(A!$A$6,A!$A$2:$A$8,A!$G$6,5)

You have seven categories but only one value, so Excel will use the first
value, and therefore only the first category.

- Jon

 

[Jon Peltier]

A series has no Count property, but the SeriesCollection does. You need
either

Dim xSer As Series
For Each xSer In aChart.SeriesCollection
' blah
Next

which I suggested, or

Dim i As Integer
For i = 1 to aChart.SeriesCollection.Count
' blah
Next

- Jon