MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vba chart


Posted by Stephanie on July 17, 2001 11:33 AM

why doesn't this work?#)$*#)!?

Charts("Pegged Prices - All Comps").SeriesCollection.Add Source:= _
Worksheets("Comps Prices").Range(Cells(FirstDateRow, 2), Cells(LastDateRow, 2))

something with my "Cells"... ??

i had
Charts("Pegged Prices - All Comps").SeriesCollection.Add Source:= "Comps Prices!R" & FirstDateRow & "C2:R" & LastDateRow & "C2"
which didn't work, but when the source was from "Comps" (later renamed to "Comps Prices" it did work.

aah! evil chart objects!


Posted by Damon Ostrander on July 17, 2001 2:27 PM

Stephanie,

The reason why this doesn't work is that, just like the Range method, the Cells method also requires the worksheet qualifier if the range (cell) is not on the active worksheet.

An easy way to qualify both the Range and Cells methods is to use the With statement:

With Worksheets("Comps Prices")

Charts("Pegged Prices - All Comps").SeriesCollection.Add Source:= _
.Range(.Cells(FirstDateRow, 2), .Cells(LastDateRow, 2))

End With

and not that all the periods (".") are necessary.

Happy charting.

Damon