Skip to content

Instantly share code, notes, and snippets.

@airstrike
Created July 4, 2020 03:37
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save airstrike/6e134e11cd03cdcc91201bfa364e6383 to your computer and use it in GitHub Desktop.
Save airstrike/6e134e11cd03cdcc91201bfa364e6383 to your computer and use it in GitHub Desktop.
Find-Replace formulas in Excel chart
Sub FindReplaceinChart()
Dim mySrs As Series
Dim iPts As Long
Dim iSrs As Long
Dim aLAbel As DataLabel
Dim sFormula As String
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
Else
Dim old_ As String, new_ As String
old_ = Application.InputBox("Replace:", "Find-Replace in Series")
If Trim(old_ & vbNullString) = vbNullString Or Trim(old_ & vbNullString) = False Then GoTo ExitSub
new_ = Application.InputBox("Replace """ & old_ & """ with:", "Find-Replace in Series")
If Trim(new_ & vbNullString) = vbNullString Or Trim(new_ & vbNullString) = False Then GoTo ExitSub
For Each mySrs In ActiveChart.SeriesCollection
sFormula = WorksheetFunction.Substitute(mySrs.Formula, old_, new_)
mySrs.Formula = sFormula
Next
End If
ExitSub:
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment