How to use Cells property to Range object?

I need to add a bunch of series to a chart. I want to use a loop to do so
and set the properties of the new series like this:

For x To y
Set objSeries = objChart.SeriesCollection.NewSeries
With objSeries
.Values =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range(Cells(2, 4), Cells(10,
4))
.[set other series properties]
End with
Next x

But the range I'm trying to assign to the Values property of the new series
throws an error:

1004 Application-defined or object-defined error

If I use Range("D2:D20") it works fine, but I want to use the Cells property
within the Range property to define the Range object - so I can increment
the row and column index within the loop.

Why is Range(Cells(2, 4), Cells(10, 4)) not working?

Thanks in advance.

Have you tried this?

For x To y
Set objSeries = objChart.SeriesCollection.NewSeries
With objSeries
Set rng = xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Values = rng.Range(rng.Cells(2, 4), rng.Cells(10,4))
.[set other series properties]
End with
Next x


--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <deko@hotmail.com> wrote in message
news:tFdXd.5219$C47.1693@newssvr14.news.prodigy.com...
> I need to add a bunch of series to a chart. I want to use a loop to do so
> and set the properties of the new series like this:
>
> For x To y
> Set objSeries = objChart.SeriesCollection.NewSeries
> With objSeries
> .Values =
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range(Cells(2, 4), Cells(10,
> 4))
> .[set other series properties]
> End with
> Next x
>
> But the range I'm trying to assign to the Values property of the new
series
> throws an error:
>
> 1004 Application-defined or object-defined error
>
> If I use Range("D2:D20") it works fine, but I want to use the Cells
property
> within the Range property to define the Range object - so I can increment
> the row and column index within the loop.
>
> Why is Range(Cells(2, 4), Cells(10, 4)) not working?
>
> Thanks in advance.
>
>

> Have you tried this?
>
> For x To y
> Set objSeries = objChart.SeriesCollection.NewSeries
> With objSeries
> Set rng = xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
> .Values = rng.Range(rng.Cells(2, 4), rng.Cells(10,4))
> .[set other series properties]
> End with
> Next x

That works great! Thanks!

I have an object defined in scope that holds the worksheet, so it looks
likes this:

..Values = objSheet.Range(objSheet.Cells(2, 4), objSheet.Cells(10, 4))

So I guess 'Range(Cells(2, 4), Cells(10, 4))' is meaningless because the app
does not know where the Cells are - at least in the context of automation.

Thanks for the help!

j = 3 'start at column 4
Do While j < lc 'lc = last column
Set objSeries = objChart.SeriesCollection.NewSeries
With objSeries
.Values = objSheet.Range(objSheet.Cells(2, (j + 1)),
objSheet.Cells(lr, j + 1))
.Name = objSheet.Cells(1, (j + 1))
End With
j = j + 1
Loop

The Values property gets set successfully, but subsequent properties throw
an error:

1004 Unable to set the Name property of the Series class

I get the same error with other properties, e.g. ".Border.Weight = xlThin",
etc.

Why can't I set the properties?

Exactly!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <deko@hotmail.com> wrote in message
news:sBlXd.12568$Pz7.3266@newssvr13.news.prodigy.com...
> > Have you tried this?
> >
> > For x To y
> > Set objSeries = objChart.SeriesCollection.NewSeries
> > With objSeries
> > Set rng = xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
> > .Values = rng.Range(rng.Cells(2, 4), rng.Cells(10,4))
> > .[set other series properties]
> > End with
> > Next x
>
> That works great! Thanks!
>
> I have an object defined in scope that holds the worksheet, so it looks
> likes this:
>
> .Values = objSheet.Range(objSheet.Cells(2, 4), objSheet.Cells(10, 4))
>
> So I guess 'Range(Cells(2, 4), Cells(10, 4))' is meaningless because the
app
> does not know where the Cells are - at least in the context of automation.
>
> Thanks for the help!
>
>
>