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)).Rang e(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.

How to use Cells property to Range object?
 

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" wrote in message
m...
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)).Rang e(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.

How to use Cells property to Range object?
 

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!

How to use Cells property to Range object?
 

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?

How to use Cells property to Range object?
 

Exactly!

--

HTH

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


"deko" wrote in message
om...
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!