BuJay
Board Regular
- Joined
- Jun 24, 2020
- Messages
- 73
- Office Version
- 365
- 2019
- 2016
- 2013
- Platform
- Windows
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 "NonPort" word
i = InStr(srs.Formula, "Total_")
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, "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 iteration generates the below:
The strange this 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!
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 "NonPort" word
i = InStr(srs.Formula, "Total_")
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, "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 iteration generates the below:
The strange this 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!