1

I have a worksheet with about 500 charts in excel. I created them manually, 1 at a time, over many hours.

All of the charts rely on named ranges to define their series.

All of the named ranged are being pulled from the same sheet, namely, the portfolio_results sheet.

For example, I have the following named ranges

Total_Portfolio_Units_Pct

Purchase_Portfolio_Units_Pct

Rate_Term_Refi_Portfolio_Units_Pct

etc.

I have like 30 charts which reference named ranges that begin with "Total_" I also need 30 charts with reference the similarly named ranges but these named ranges start with "Purchase_" instead of "Total_".

So, the macro below allows be to update the "Total_" to "Purchase_" else so that I don't have to manually go into every chart and manually change the series definitions.

Sub updateChart()

Dim sh As Worksheet
'set which sheet the charts are
Set sh = ActiveSheet
'For each chart on the selected sheet
For Each ch In sh.ChartObjects
    'for each series on the selected chart from the loop above (if there's more than one series of values)
    For Each srs In ch.Chart.SeriesCollection
        'check if the series has the word
        i = InStr(srs.Formula, "Total_")
        Debug.Print i
        'if i is greater than 0 it means that the series has the word 
        If i > 0 Then
            'replace the old word with the new word
            newSrs = Replace(srs.Formula, "Total_", "Purchase_")
            Debug.Print newSrs
            'update the series
            srs.Formula = newSrs
        End If
    'next series
    Next
'next chart
Next
End Sub

The macro works perfectly for all of the charts except for the following and I cannot understand why it doesn't work.

I even added debug.print steps to see what the macro was generating in the iterations and I confirmed that the 3rd or 4th iteration generates the below:

depug.print output

The strange thing is....that SERIES formula is correct! It correctly references the "portfolio_results!Rate_Term_Refi_Portfolio_Units_Pct" named range.

When I enter this name into a cell elsewhere in excel, the array shows up.....

Any thoughts?

I am at my wits end....

Thanks!

The macro I have works on 90% of my charts but not on others - trying to figure out why! I added depug.print step to examine the interim references and they look correct.

braX
  • 11,506
  • 5
  • 20
  • 33
BHF
  • 35
  • 7
  • I have an excel that produces the error but not sure how to attach here. – BHF Jan 21 '23 at 02:31
  • Are all the charts on the same sheet as the named range? Error 1004 occurs when you use VBA code to select a named range that is not in the active worksheet. – user10186832 Jan 21 '23 at 06:17
  • Your code shows `Replace(srs.Formula, "Total_", "Purchase_")` but your example is for `Rate_Term_Refi` ? – CDP1802 Jan 21 '23 at 10:29
  • All of the named ranges are on the sheet called portfolio_results. All of the charts are on a different sheet called portfolio_charts. I have similar sheets called investor_results and investor_charts where I want to repeat this process. – BHF Jan 21 '23 at 12:40

1 Answers1

0

Quite a strange issue....

Apparently, if my charts reference a named range that starts with certain letters, Excel throws an error. however, if I change the named ranges to start with a different letter, it works.

For example, if I change the Rate_Term_Refi named range to xRate_Term_Refi, it works.

Sub updateChart()

Dim sh As Worksheet
'set which sheet the charts are
Set sh = ActiveSheet

'For each chart on the selected sheet
For Each ch In sh.ChartObjects
    'for each series on the selected chart from the loop above (if there's more than one series of values)
    For Each srs In ch.Chart.SeriesCollection
        'check if the series has the "NonPort" word
        i = InStr(srs.Formula, "Purchase_")
        'Debug.Print i
        
        'if i is greater than 0 it means that the series has the word "NonPort"
        If i > 0 Then
            'replace the word from NonPort to Port
            newSrs = Replace(srs.Formula, "Purchase_", "xRate_Term_Refi")
            'Debug.Print newSrs
            'update the series
            srs.Formula = newSrs
        End If
    'next series
    Next
'next chart
Next

End Sub
BHF
  • 35
  • 7