Method 'Cells' of object '_Global' failed - VBA

[C Brandt]

Try as I might, I can not figure this one out.

I am building an embedded chart that uses data from another sheet. When I
tried to add the data to the chart, the system has a run-time error 1004 (
Method 'Cells' of object '_Global' failed)
The line that generated the error was:

co.Chart.SeriesCollection.Add Source:=Sheets("DB").Range(Cells(ChrtStrt, 9),
Cells(ChrtEnd, 12))

ChrtStrt & ChrtEnd are both valid numbers. I do not understand what is
causing this error.
Any suggestions?

Thanks,

Craig

 

[Dave Peterson]

You are using an unqualifed range.

Depending on where the code is located, cell(chrtstrt,9) either refers to the
activesheet (in a general module) or the sheet owning the code (in a worksheet
module).

I'd use:

with worksheets("DB")
co.Chart.SeriesCollection.Add _
Source:=.Range(.Cells(ChrtStrt, 9),.Cells(ChrtEnd, 12))
end with

But you could use:
co.Chart.SeriesCollection.Add _
Source:=Sheets("DB").Range(Sheets("DB").Cells(ChrtStrt, 9), _
Sheets("DB")Cells(ChrtEnd, 12))