Solved: Pulling hair here... :D

[sardaukar]

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?


Thank you for your time and any replies!

[Ken Puls]

Try this:
[vba]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[/vba]

[vonpookie]

It is not working because you have not specified the sheet for the second cell. For example:
[vba]Sheets("Pivot Chart").Range(Cells(2, 1), Sheets("Pivot Chart").Cells(LastUsedLine, 2))[/vba]

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]

It worked!! Thanks!

[Ken Puls]

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]

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!