[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA to change formula in data label

Posted on 2013-12-17
3
Medium Priority
?
1,799 Views
Last Modified: 2016-06-14
Hello,

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!!!!
0
Comment
Question by:ejortberg
3 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1600 total points
ID: 39725908
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

0
 

Author Closing Comment

by:ejortberg
ID: 39727180
Fantastic - you saved my holiday break!
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 41652560
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question