How to use Cells property to Range object?

[deko]

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("D220") 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.

 

[Bob Phillips]

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]

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!

 

[deko]

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?

 

[Bob Phillips]

Exactly!

--

HTH

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

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!