Setting a chart source data based on a variable cell range in VBA

Question

I am creating a report template part of which will be generating data charts with a structure similar to a moving average

for this I need to select a range of data in one sheet (e.g. f10:i14) and place and set it as the source of one chart

The following piece of code works

ActiveChart.SetSourceData Source:=Sheets("Weekly Trends").Range("f10,i14")

but this does not

ActiveChart.SetSourceData Source:=Sheets("Weekly Trends").Range(Cells(x, y), Cells(k, z))

it returns a 1004 error

But I need to specify the range based on variable indices for my report purposes, please help

[can-variables-be-used-to-select-fully-qualified-range](http://www.mrexcel.com/forum/excel-questions/86483-can-variables-used-select-fully-qualified-range.html)? :) — bonCodigo, Jan 18 '13 at 03:07

Siddharth Rout · Accepted Answer · 2013-01-17T22:14:51.787

15

You have to fully qualify the .Cells object

Try this

ActiveChart.SetSourceData Source:=Sheets("Weekly Trends").Range(Sheets("Weekly Trends").Cells(x, y), Sheets("Weekly Trends").Cells(k, Z))

You can also make the above code short

With Sheets("Weekly Trends")
    ActiveChart.SetSourceData Source:=.Range(.Cells(x, y), .Cells(k, Z))
End With

edited Jan 17 '13 at 22:14

answered Jan 17 '13 at 22:00

Siddharth Rout

This works perfectly, thank you! I assumed that qualifying the sheet would be enough. – Ahmad A Hammoudi Jan 18 '13 at 15:16

1 Answers1