Replace for Chart Formulas?

[kbrownk]

Does anyone know if there's a way to replace letters or numbers in series formulas within charts? Seems obvious but I've had no luck googling.

If an example is needed:

I have 2 series in a scatter plot:
=SERIES(Sheet1!$A$1,Sheet1!$C$1:$M$1,Sheet1!$C$2:$M$2,1)
=SERIES(Sheet1!$A$3,Sheet1!$C$3:$M$3,Sheet1!$C$4:$M$4,1)

I'd like to replace all "C" with "O" and "M" with "Y".

In reality I have 68 series so you can see why I don't want to do this manually!

Thanks,
kbrownk

 

[littleiitin]

1:Change "Master" with your sheet Name
2: Change "Chart 1" with your Chart Name

Sub changeaddress()
    With ThisWorkbook.Worksheets("Master")
        With .ChartObjects("Chart 1").Chart
            For i = 1 To .SeriesCollection.Count
               .SeriesCollection(i).Formula = Replace(.SeriesCollection(i).Formula, "$C", "$O")
               .SeriesCollection(i).Formula = Replace(.SeriesCollection(i).Formula, "$M", "$Y")

            Next
        End With
    End With
End Sub

 

[kbrownk]

Perfect, thanks! This will definitely save me time now and in the future.

kbrownk