Use of Range(Cell1,Cell2) in chart creation
 

I'm trying to create charts of various data sets using a calculated
dynamic range. However "SetSourceData Source" seems to be limited in
the types of expression it will allow for ranges. For example:

ActiveChart.SetSourceData Source:=Sheets("One").Range("D5:E29')
is OK, but

ActiveChart.SetSourceData Source:=Sheets("One").Range(Cells(5, 4),
Cells(29, 5)),
tells me that "Method 'Cells' of object '_Global' failed".

I need to be able to use the second technique, or some variant of it.
Could someone please suggest a work-around? (BTW,
Sheets("One").Selection , where the selection has been pre-organised,
also seems to be forbidden by VBA. Pity, that would have been an ideal
solution)

TIA

Use of Range(Cell1,Cell2) in chart creation
 

Hi,

You need to fully qualify your objects.
If this code is running whilst the activechart is a chart sheet then
Cells object will fail.

ActiveChart.SetSourceData _
Source:=Sheets("One").Range(Sheets("One").Cells(5, 4), _
Sheets("One").Cells(29, 5))

Cheers
Andy

jeh wrote:
I'm trying to create charts of various data sets using a calculated
dynamic range. However "SetSourceData Source" seems to be limited in
the types of expression it will allow for ranges. For example:

ActiveChart.SetSourceData Source:=Sheets("One").Range("D5:E29')
is OK, but

ActiveChart.SetSourceData Source:=Sheets("One").Range(Cells(5, 4),
Cells(29, 5)),
tells me that "Method 'Cells' of object '_Global' failed".

I need to be able to use the second technique, or some variant of it.
Could someone please suggest a work-around? (BTW,
Sheets("One").Selection , where the selection has been pre-organised,
also seems to be forbidden by VBA. Pity, that would have been an ideal
solution)

TIA


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Use of Range(Cell1,Cell2) in chart creation
 

Thanks Andy.
Cheers
John