Problem with using R and C in charting macro

[ajwillshire]

Hi,

I've got a macro which creates dynamic ranges and adds them to a chart. As part of it a User Form offers the chance to prefix the Dynamic Name before charting.

However, if I use a prefix beginning with R or C it falls over. I know that R or C followed by an integer is forbidden but as far as i can see that doesn't occur.

Here's the line which trips it up:

ActiveChart.SeriesCollection.NewSeries.Formula = "=SERIES(" & Chr(34) & mySeriesName & Chr(34) & "," & wksName & "!" & Prefix_ns & "Dates," & wksName & "!" & myName & "," & i & ")"

mySeriesName is the name that appears in the legend - say "October Spend".
wksName is the worksheet name - "Sheet2" in my example
Prefix_ns is the prefix e.g., "Roger_"
myName is the name of the dynamic range - "October_Spend"
i is the index of the loop to give the series number in the chart.


So the formula that it should be is
"=Series("October Spend", 'Sheet2'!Roger_Dates, '"Sheet2'!Roger_October_Spend, 1)

But it doesn't work if the prefix begins with R or C, So Roger, Rhys, Colin, etc all trip it up while all other names work perfectly.

Any ideas?
Thanks in advance,
Andrew

 

[Andy Pope]

Try setting the parts of the newseries rather than the Formula

Sub zz()

    mySeriesName = "October Spend"
    wksName = "Sheet2"
    Prefix_ns = "Roger_"
    myName = "October_Spend"
    i = 1
    With ActiveChart.SeriesCollection.NewSeries
        .Name = mySeriesName
        .Values = "='" & wksName & "'!" & myName
        .XValues = "='" & wksName & "'!" & Prefix_ns & "Dates"
        .PlotOrder = i
    End With
    
End Sub

 

[GlennUK]

Your code produces:
=SERIES("October Spend",Sheet2!Roger_Dates,Sheet2!October_Spend,1)
which is not what you said it produces.

 

[ajwillshire]

Hi Andy,

Thanks for your reply - it works perfectly!

(Except for the .plotorder line - that seems only to be a property of "series" and not "newseries" - but it's not important for my needs).

Thanks for your time,
Andrew

 

[ajwillshire]

Hi Glenn,

You're right! But it was my mistake in writing it in this posting. myName is prefixed previously in the code so it is actually "Roger_October_Spend" by the time it gets to the chart formula.

Thanks for your time though. I'm still baffled by why the names beginning with R and C threw errors but Andy's fix works fine.

Cheers,

Andrew

 

[GlennUK]

I'm glad Andy's fix works fine ... I have been reading his examples for a long time, a great inspiration. ( http://www.andypope.info/charts.htm )