MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with Range objects please!


Posted by Ron Dionne on October 24, 2001 3:44 PM

I want to loop thru a series of columns on a number
of worksheets to create some charts. As a start, I
used this code to add a new series to the chart:

lineChart.SeriesCollection.Add Source:=newWB.Worksheets(i).Range("C7:C31")

It works fine. So as a try to switch from the A1 named
range to one that I can use with variables, I tried to
specify the range using the Cells like this:

lineChart.SeriesCollection.Add Source:=newWB.Worksheets(i).Range(Cells(7, 3), Cells(31, 3))

Now I get an runtime error 1004 (application-defined
or object-defined error). I ultimately want to specify
the ranges using variables like this:

lineChart.SeriesCollection.Add Source:=newWB.Worksheets(i).Range(Cells(a, b), Cells(c, d))

What am I missing here? Is there a better way to work with ranges and variables?


Posted by Bob Umlas on October 24, 2001 4:38 PM

Try this:
Instead of
lineChart.SeriesCollection.Add Source:=newWB.Worksheets(i).Range(Cells(7, 3), Cells(31, 3))
use
With newWB.Worksheets(i)
lineChart.SeriesCollection.Add Source:=.Range(.Cells(7, 3), .Cells(31, 3))
End With
---Notice the "." before "Range" and "Cells".
The way you had it, Excel was referring to Cells(7,3) but was not part of newWB.Worksheets(i).Cells(7,3).

HTH
----------------------------