Finding Chart Objects Based on their series

[tias]

I need to find all chart objects that use a particular range as a value for a sequence so that I can update them to use a different range instead.

The following is the code I already have

Option Explicit
Sub testUpdateCharts()
    updateCharts Range("A1:A10"), Range("B1:B10")
End Sub

Sub updateCharts(oldvals As Range, newvals As Range)
    Dim chrt As Chart
    'Iterate ALL the charts
    For Each chrt In ThisWorkbook.Charts
        updateChart chrt, oldvals, newvals
    Next chrt
    Dim sheet As Worksheet
    For Each sheet In ThisWorkbook.Worksheets
        Dim t As Variant
        For Each t In sheet.ChartObjects
            Set chrt = t.Chart
            updateChart chrt, oldvals, newvals
        Next t
    Next sheet
End Sub
Sub updateChart(chrt As Chart, oldvals As Range, newvals As Range)
    Debug.Print "UpdateChart: " & chrt.Name
    Dim srs As Series
    'Iterate all the series in the chart
    For Each srs In chrt.SeriesCollection
        Debug.Print "UpdateChart: " & chrt.Name & "::" & srs.Name
        'If srs.Values = oldvals Then 'error 13 Type miss match
        If srs.Values.Cells(1, 1) = oldvals.Cells(1, 1) Then 'errors 424 object required
            Set srs.Values = newvals
        End If
    Next srs
    Exit Sub
End Sub

The comparison fails with an error; caused either by a type miss-match[13] (direct comparison) or object expected[424] (when comparing the top-left cell only)

Any enlightenment as to why the code doesn't seem to work would be greatly appreciated

 

[tusharm]

Check Excel VBA help for the series.Values. You will find it returns an array with the actual values not a range object.

 

[tias]

Many thanks for pointing out my error;
I tested to see what the type of the array is to find that it is an array of values as you had indicated. (although this seems somewhat at odds with the scant documentation microsoft provides on the issue: http://msdn.microsoft.com/en-us/library/bb238636.aspx)

any suggestions or pointers how I might actually find a range object indicating what cells this series is using as data?

Many thanks again, 'Tias

 

[tusharm]

The only way I know is to parse the Series formula. I have a comprehensive class to do that but it is not ready for sharing with the world. John Walkenbach has a parser on his site that will work for all but the more exotic ranges. See http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

 

[tias]

Yeah, I just came to the same conclusion after far too much googling and now have a half working functions to do the job.
I derived my code from
http://www.j-walk.com/ss/excel/tips/tip83.htm
but this particular code will break if any of the parts of the formula contain a comma (eg when the sheet name contains a comma)