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!
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!