VBA to change formula in data label


I used Rob Bovey's xy chart labeler to label a dataseries in a scatterplot. Each datalabel refers to a cell in the worksheet, such as "=BE_Cat!$D$30", so the data label contains whatever is in cell d30, d31 etc.

Unfortunately, I (or Excel) screwed up and have many, many files and charts where the label formula refers to the wrong sheet, e.g. "=CV_Cat!$D$30" instead of "=BE_Cat!$D$30".

To fix this, I'm looking for a macro to select a certain dataseries in a chart (Let's call it "CV"), and then circle through all the data labels in the series to change "CV" to "BE" in the label formula, i.e. from  "=CV_Cat!$D$30" to "=BE_Cat!$D$30", "=CV_Cat!$D$31" to "=BE_Cat!$D$31", and so on.

Thank you!!!!
Who is Participating?
andrewssd3Connect With a Mentor Commented:
This will work for the active chart (i.e. the clicked-on selected chart):
Public Sub ReformatLabels()

    Dim c As Chart
    Dim s As Series
    Dim p As Point
    Const FROM_STR As String = "CV"
    Const TO_STR As String = "BE"
    Set c = ActiveChart

    Set s = Nothing
    On Error Resume Next
    Set s = c.SeriesCollection("CV")
    On Error GoTo 0
    If Not s Is Nothing Then
        For Each p In s.Points
            If p.HasDataLabel Then
                p.DataLabel.Formula = Replace(p.DataLabel.Formula, FROM_STR, TO_STR)
            End If
        Next p
    End If
End Sub

Open in new window

ejortbergAuthor Commented:
Fantastic - you saved my holiday break!
Jon_PeltierExcel DeveloperCommented:
You may include a check for an actual formula

If Left$(p.DataLabel.Formula, 1) = "=" Then

Open in new window

since .Formula will return the data label's .Text if it isn't a "real" formula.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.