defined names and setsourcedata

[Jason Morin]

I have formula in a cell that creates a string. The string can represent
several defined names strung together, delimited by a comma. For example, the
formula in cell D21 returns:

AX,SY,KS

AX is actually a defined name for a range A1:A10 (the axis), SY = C1:C10,
and KS = E1:10

I'm trying to set the source data range in a chart on a different sheet to
the string I created in D21. Even if I name D21 as "makechart", it still
won't work.

Sub Macro5()
ActiveChart.SetSourceData Source:=ActiveWorkbook.Names("makechart"),
PlotBy:=xlColumns
End Sub

Please help.
Thanks.
Jason

 

[Bob Phillips]

Dim rng As Range

Set rng = Union(Range("AX"), Range("SY"), Range("KS"))

ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jon Peltier]

Bob's suggestion works fine. I wanted to explain why your one-liner doesn't
work.

SetSourceData takes a range as its Source argument.
ActiveWorkbook.Names("makechart") is a name, not a range. You could get
either of these to work:

ActiveChart.SetSourceData Source:=ActiveSheet.Range("makechart")
ActiveChart.SetSourceData
Source:=ActiveWorkbook.Names("makechart"),RefersToRange

- Jon