PDA

View Full Version : Solved: Pulling hair here... :D



sardaukar
04-24-2007, 08:00 AM
DISCLAIMER: total VBA n00b

I want to define a Range to use as a DataSource for an Excel Chart, but I believe I'm having access qualification issues.

What I have now (that works) is this ugly thing:


LastUsedLine = Sheets("Pivot Chart").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Charts.Add
ActiveChart.ChartType = xlLineMarkers

ActiveChart.SetSourceData Source:=Sheets("Pivot Chart").Range("A2:B" & LastUsedLine), _
PlotBy:=xlColumns


This gives me a "hand built" range, but I was hoping to use something more elegant, like:


LastUsedLine = Sheets("Pivot Chart").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Charts.Add
ActiveChart.ChartType = xlLineMarkers


ActiveChart.SetSourceData Source:=Sheets("Pivot Chart").Range(Cells(2, 1), Cells(LastUsedLine, 2)), _
PlotBy:=xlColumns

But I get an error 1004, "Method "Cells" of object "_Global" failed".I'm missing something to bind the Cells to my sheet or something, right? Right? :D


Thank you for your time and any replies!

Ken Puls
04-24-2007, 08:52 AM
Try this:
LastUsedLine = Sheets("Pivot Chart").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Charts.Add
ActiveChart.ChartType = xlLineMarkers

With Sheets("Pivot Chart")
ActiveChart.SetSourceData Source:=Range(.Cells(2, 1), .Cells(LastUsedLine, 2)), _
PlotBy:=xlColumns
End With

vonpookie
04-24-2007, 08:58 AM
It is not working because you have not specified the sheet for the second cell. For example:
Sheets("Pivot Chart").Range(Cells(2, 1), Sheets("Pivot Chart").Cells(LastUsedLine, 2))

When you don't specify the sheet for a range, VBA defaults to treating it as a range on the currently active sheet. So the first cell (cells(2,1)) is correctly referenced as being on the Pivot Chart sheet, but the second cell (cells(LastUsedLine, 2)) does not have a qualifier and VBA is defaulting to thinking you are referencing the active sheet for that.

But really, I wouldn't say that your second example is any less "elegant" than the first--they're doing the exact same thing, just with slightly different code (and with the first example, you don't have to specify the sheet twice ;) ).

sardaukar
04-24-2007, 09:00 AM
It worked!! Thanks!

Ken Puls
04-24-2007, 09:02 AM
But really, I wouldn't say that your second example is any less "elegant" than the first--they're doing the exact same thing, just with slightly different code

I would tend to agree there. The other thing to consider is that next month,/year when you're looking back on this to modify it, which is easier to read? ;)

sardaukar
04-24-2007, 09:04 AM
But really, I wouldn't say that your second example is any less "elegant" than the first--they're doing the exact same thing, just with slightly different code (and with the first example, you don't have to specify the sheet twice ;) ).

I find it much more readable to have the range showing how it is being built on the range itself and not having it be "pre-processed". Also, the "&" operator usage in VBA is kinda obscene - it gets thrown around way too often and it's not immediate how it works.

Thanks for the technical info, and thanks for your help!