We help IT Professionals succeed at work.

Excel:  Looping through all chart series of all charts on a worksheet

skillilea
skillilea used Ask the Experts™
on
I need to be able to loop through all my charts (without selecting them) and modify the chart series value.  I can't seem to get at the value or xvalues property of the series.  What am I doing wrong?

Thanks experts!
Private Sub reset_chartseries()    
    Dim c As ChartObject
    Dim n As Integer
    Dim cs As SeriesCollection

    For Each c In rws.ChartObjects
        Set c = rws.ChartObjects(c.Name)
            For n = c.Chart.SeriesCollection.Count To 1 Step -1
                Set cs = c.Chart.SeriesCollection
                    Debug.Print cs(n).Values

'here is where I will do an if statement
' if value = "[0]" then xvalue ="reports!named range"

            Next
    Next
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can try replacing:

' if value = "[0]" then xvalue ="reports!named range"

with:

If valu = "[0]" then xvalue = Range("reports!named range").Value

Open in new window

Author

Commented:
Let me be more clear.

How do I access the property of the chart series collection.  I can't get the value out so I can test if it is bad.

Author

Commented:
OK really looking for some input here.

I am trying to access the values of a charts series collection to test what they are.  The below code gives me an object required error.   What am I missing?
Sub Test2()
    Dim DataRange As Range
    Set DataRange = Sheets("reports").ChartObjects("DivChart1").Chart.SeriesCollection(1).Values
    Debug.Print DataRange.Address
End Sub

Open in new window

The DataRange variable you have declared can contain the values of datatype Range only. The values you are trying to assign to the same is not a valid value for Range type variable.

To grab the values in the SeriesCollection(1) you can use below code:

Series_values = Sheets("reports").ChartObjects("DivChart1").Chart.SeriesCollection(1).Values

and access the values in the above series with below code:

For Each S_Value In Series_values
      Debug.Print S_Value
Next

Author

Commented:
I still get an object required with the below code.

Sub Test2()
    Dim Series_values As SeriesCollection
    Set Series_values = Sheets("reports").ChartObjects("DivChart1").Chart.SeriesCollection(1).Values

   ' Debug.Print Series_values
End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011
Commented:
Values returns an array not an object.
Private Sub reset_chartseries()    
    Dim c As ChartObject
    Dim n As Integer
    Dim cs As SeriesCollection
    Dim varData

    For Each c In rws.ChartObjects
        For each cs in c.Chart.SeriesCollection
           varData = cs.Values
           For n = lbound(vardata) to ubound(vardata)
              if varData(n) = 0 then
                 ' do whatever
              End if
           Next n
         next cs
    Next c
End Sub

Open in new window

skillilea,

Variable Series_values was not supposed to be a SeriesCollection object. Code provided by me will work if you use Series_values as a varient. Then you can catch where value is Zero.
Same as given in the code provided by Rorya.

Author

Commented:
I am still getting a type mismatch error on the series collection.  Sorry, what am I doing wrong?
Sub Test2()
    Call set_var
  Dim c As ChartObject
    Dim n As Integer
    Dim cs As SeriesCollection
    Dim varData As Variant

    For Each c In rws.ChartObjects
        For Each cs In c.Chart.SeriesCollection
           varData = cs.Values
           For n = LBound(varData) To UBound(varData)
              Debug.Print varData(n)
              If varData(n) = 0 Then
                 ' do whatever
              End If
           Next n
         Next cs
    Next c

End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011

Commented:
My fault - change this:
 Dim cs As SeriesCollection
to this:
 Dim cs As Series

Author

Commented:
Rorya

Sorry, I feel like I am wasting your time.  My issue is this:   I pull data from another workbook and when I clear the sheet it breaks my graphs.  When the graphs are working the series is this:   reports!F_graph_data1.  When it breaks the xvalue ends up as [0]!F_graph_data1.  My thought was to loop through the series and find the [0] and replace it.  

Am I asking the wrong thing cause now I get all the values not the actual   reports!F_graph_data1?

Author

Commented:
Maybe I use cs.formula and test that?
Most Valuable Expert 2011
Top Expert 2011

Commented:
How do you clear the sheet? Deleting cell contents should not break your charts.

Author

Commented:
I use cells.clear and then repopulate.  I actually am very frustrated cause it feels very weird why it breaks.  The charts are all dynamic  using formulas like   =OFFSET(FIN_DivGraphStart, CONTROL!$D$123, CONTROL!$F$123, 1, CONTROL!$G$123).   The control cells all go to #N/A  cause they are calculated off the data in the sheet I clear.  That is why I am assuming the charts break.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Why don't you simply reset all the series to what they should be?
Are you using Excel 2007 and if so do you have Service Pack 2 installed?

Author

Commented:
Thanks tons!  Again, you experts save me a ton of time.