Hi All,

I want to change the range references for 2 series of a chart using VBA.

The Chart name (after doing Shift + LeftClick) is "Chart 44".

It has 2 series:
  • Actual
=SERIES('Section 4 - Case Management2'!$A$218,'Section 4 - Case Management2'!$B$216:$S$216,'Section 4 - Case Management2'!$B$218:$S$218,1)
and
  • Forecast
=SERIES('Section 4 - Case Management2'!$A$219,'Section 4 - Case Management2'!$B$216:$S$216,'Section 4 - Case Management2'!$B$219:$S$219,2)
I want to change actual formula references from $218 to $252 and forecast from $219 to $259.

I have tried the following code (as adapted from Jon Peltier's helpful site):

[vba]Sub ChangeWorkbookChartFormula()

Dim chtObj As Excel.ChartObject
Dim wksht As Excel.Worksheet
Dim mySrs As Series

For Each chtObj In ThisWorkbook.Worksheets("Section 4 - Case Management2").ChartObjects

Debug.Print UCase(chtObj.Name)

If UCase(chtObj.Name) = "CHART 44" Then

For Each mySrs In chtObj.Chart.SeriesCollection

Select Case UCase(mySrs.Name)

Case "ACTUAL"

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "$218", "$252")


Case "FORECAST"

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "$219", "$259")

End Select

Next mySrs

End If

Next chtObj

End Sub[/vba]
there is no error and it is recognising teh charts and series as I Step through, but no change occurs in the series ranges!

Could anyone please help to correct for this?

I can't post the workbook up due to confidentiality.