View Single Post
  #1  
Old May 18th, 2010, 05:33 PM posted to microsoft.public.excel.charting
Gill L
external usenet poster
 
Posts: 1
Default Excel 2007 - problems with macro to change series formula

Please help! I've just been moved to Excel 2007 and now find that the
wonderful macro below no longer works (the single chart version is still OK).
I have files with 50 charts on a single worksheet (the data is on a separate
tab) and I need to choose which rows to include rather than having an auto
update. The files are accessed from multiple workstations so a macro saved
in the workbook was ideal.
Is there any way I can modify this code to get it working? Thanks in advance.


Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old
string")

If Len(OldString) 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula,
OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub