Range Syntax Excel VBA

[JaHarding]

I'm charting in Excel using VBA.

If I use something like:

ActiveChart.SetSourceData Source:=Worksheets("Sheet2").Range("A1:D10"), PlotBy:=xlColumns

Then it plots.

But if I use something like:

ActiveChart.SetSourceData Source:=Worksheets("Sheet2").Range(Cells(1, 1), Cells(10, 4)), PlotBy:=xlColumns

Then I get the method 'cells' of object '_global' failed
error message.

I want to use the Range(Cells... syntax so the chart will reflect changes in the numbers of columns, etc.

Is the 2nd syntax no longer valid? (Excel 2000).

 

[sfvb]

You need to qualify the reference to Cells. Ex:

ActiveChart.SetSourceData Source:=Worksheets("Sheet2&quot.Range(Worksheets("Sheet2&quot.Cells(1, 1), Worksheets("Sheet2&quot.Cells(10, 4)), PlotBy:=xlColumns

 

[JaHarding]

Thank you.

I decided to try the .UsedRange approach and this works as long as the spreadsheet is not "contaminated".

Nevertheless, your solution makes sense and I'm sure I'll be applying it elsewhere.

Thanks again.