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

Siddharth Rout · Accepted Answer · 2013-01-17 22:14:51Z

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

answered Jan 17, 2013 at 22:00

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This works perfectly, thank you! I assumed that qualifying the sheet would be enough.