3 cells are named together - how refere to them in one chart reference field (a seriescollection(1)

[Marie J-son]

Hi,

I have three cells named and want to use that name in a vba chart procedure
for a xl3DColumnStacked chart

ActiveChart.SeriesCollection(1).Values = sheet1.Range(scol1v)

I used in the procedure:
Dim scol1v As String
Let scol1v = "ThreeValues"

Meaningless note:
I used sheet1 as Name and CodeName

In the excel GUI you find the name "ThreeValues" is defined as this
reference:
'sheet1'!$A$1;'sheet1'!$A$4;'sheet1'!$A$6

I thougt it just was to specify the sheet + name, but the debbugger doesn't
like that. what am i doing wrong? Shall I use array() or something? Please
give me a syntax or procedure that works that I can look at...

Regards

 

[Jan Karel Pieterse]

Hi Marie,

Please
give me a syntax or procedure that works that I can look at...

ActiveChart.SeriesCollection(1).Values =
Activeworkbook.Names("YourName").RefersToRange

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Marie J-son]

Sounded good, but I can't make it work.
Should I use Dim as String as declaration of the Name, or Range? Do you (or
any other reading this) have some test template etc?

This worked. Is your solution the same?
ActiveChart.SeriesCollection(1).Values = "=" & ActiveWorkbook.Name & "!" &
scol1v
I don't want to use this , please help me through this...


Regards

PS.
I have a lot of charts I want to create in VBA and try to find a way to loop
through them. I have already grouped the multi-ranges etc. with Names and
there is 3 seriecollections with their name, variables and one xValues in
each chart. AS a MVP, maybe you have something to share from your
library...?

 

[Jan Karel Pieterse]

Hi Marie,

This worked. Is your solution the same?

Mine was wrong, I forgot that the seriescollection needs a string address.

ActiveChart.SeriesCollection(1).Values = "=" & ActiveWorkbook.Name & "!" &
scol1v
I don't want to use this

I fail to understand what you *do* want to use, sorry. Could you try and
explain that?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com