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

Accepted Answer

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

Question posted 2013 · +4 upvotes