Cannot figure out why this is producing an error...

[BuJay]

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!

 

[6StringJazzer]

Have you tried manually editing the series to see if that works? There are some restrictions on what you can use in a chart Series function. I have found it to be very particular about using named ranges on other sheets. They must written as named ranges within the file name for some reason. Also I have had trouble using range names that begin with some letters so I've made a habit of starting them all with "x". See image for examples of both.

First try making the change manually in the series formula and then we'll go from there.

 

[BuJay]

Interesting!!! I will try that as soon as I get back to my computer. (Prob tomorrow unfortunately….)

Thanks!

 

[BuJay]

No Way!!!!! I couldn't wait....

I cannot believe - you were exactly right,

If I change the Rate_Term_Refi named range to xRate_Term_Refi, it works....it must not like the named range starting with R

Incredible - and THANK YOU!!!!!

 

[6StringJazzer]

Now that I am remembering better, you cannot use a named range in a chart series if the named range begins with R or C.

It's a very weird and undocumented "feature" and the first time I ran across it, it took me hours to figure out what was going on.

I am so happy that my effort can help someone else!

 

[BuJay]

That is so funny - all of my issues started with R or C! Thanks again!

 

[6StringJazzer]

The assumption in the community is that this is somehow related to R1C1 cell addressing syntax, and that using an R or C somehow messes up something that Excel does internally but is not obvious to the user. I have seen many user posts about this in multiple tech forums but zero information from Microsoft.

A similar but obvious restriction is that you cannot have a name range that is a valid cell address. For example, I have a row for each car I have. I can name A1 "Camry", A2 "RAV4", but when I get to A3 I can't name it "Q60". That's a lot easier to understand.

 

[BuJay]

The assumption in the community is that this is somehow related to R1C1 cell addressing syntax, and that using an R or C somehow messes up something that Excel does internally but is not obvious to the user. I have seen many user posts about this in multiple tech forums but zero information from Microsoft.

A similar but obvious restriction is that you cannot have a name range that is a valid cell address. For example, I have a row for each car I have. I can name A1 "Camry", A2 "RAV4", but when I get to A3 I can't name it "Q60". That's a lot easier to understand.

At least there is “some”’ basis for that… thanks!