PDA

View Full Version : Charting from Range assigned using Cells method!?



NeoMat
12-05-2011, 09:38 AM
Hi all, I've been literally:banghead: for a few hours and can't figure this out with multiple multiple google searches.

I'm trying to create multiple graphs from vba. Each graph will potentially have different ranges and I'm running the "create graph function" in a loop with other data. I need to define the range in which the different graphs select their data from within these loops. That's why I can't define it like this, for example:

ActiveChart.SetSourceData Source:=Sheets("Ignore This").Range("A3 : D4"), PlotBy:=xlRows

I want to define them like this:

ActiveChart.SetSourceData Source:=Sheets("Ignore This").Range(Cells(3, 2), Cells(4, 13)), PlotBy:=xlRows

where the numbers would be replaced by variables which are looped using for/next.

I'll post the code if necessary but I think it is merely syntax in nature and therefore I just need to know how to select a range using cells by number!

Thanks in advance!

Bob Phillips
12-05-2011, 10:28 AM
Maybe



With Sheets("Ignore This")

ActiveChart.SetSourceData Source:=.Range(.Cells(3, 2), .Cells(4, 13)), PlotBy:=xlRows
End With

NeoMat
12-05-2011, 12:00 PM
You sir, are god.

Thanks so much!

I'd still like to know why that worked and what I was doing didn't. Why was the "with" loop necessary to make it work properly?

Bob Phillips
12-05-2011, 01:14 PM
It wasn't the With per se, but the fully qualifying the Cells object to the same sheet.

I could have written it as


ActiveChart.SetSourceData Source:=Sheets("Ignore This") _
.Range(Sheets("Ignore This").Cells(3, 2), Sheets("Ignore This").Cells(4, 13)), PlotBy:=xlRows


but I used With ... End With to simplify and make the code more readable.