Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...