Plot two series collections in a chart vith VBA

[Corni]

I intended to update a chart with a macro based on some tabular data. The code in VBA is:

Dim rowNumber As Integer
Windows("Book1.xls").Activate
With Worksheets("Sheet1")
.ChartObjects(1).Activate
.ChartObjects(1).Chart.HasTitle = True
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "MY CHART"
.ChartObjects(1).Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = .Range(Cells(8, 1), _
Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
ActiveChart.SeriesCollection(1).Values = .Range(Cells(8, 3), _
Cells(rowNumber, 3)).Address(ReferenceStyle:=xlR1C1)
ActiveChart.SeriesCollection(2).XValues = .Range(Cells(8, 1), _
Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
ActiveChart.SeriesCollection(2).Values = .Range(Cells(8, 66), _
Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
End With

But when the code reaches the line which assigns XValues to the SeriesCollection(1) I get a run-time error "Method 'Cells' of object '_Global' failed. Any idea how to modify the code?

[ This Message was edited by: corni on 2002-03-05 18:44 ]

[Matanuska]

You need to assign a value to rowNumber before this will work.

[Corni]

I assigned the value for rowNumber, I simply omitted this in what I posted. In the meantime, as so many times happen I realized what was the cause for the error. All the range objects necessary to provide the Values and XValues had to be set up before the For ... Next statement, because in the For statement at that time active objects are the charts, not the worksheet. Thank you anyway for reviewing my problem.

[jonp]

I am getting the same error, but have not been able to resolve it. Could you please post the code that remedied your porblem. Where did you and how did you set up the XValues and Values before the For..Next statement. Thanks.

[Corni]

The code below was used to update chart data with XValues from the range A8:A38, Values(series1) from C8:C38 and values(series2) from BN8:BN38. The only condition not to fail range object methods is to have some data in the existing series.

Dim rowNumber As Integer, r(2) As Range
Windows("Book2").Activate
rowNumber = 38
Set r(0) = Range(Cells(8, 1), Cells(rowNumber, 1))
Set r(1) = Range(Cells(8, 3), Cells(rowNumber, 3))
Set r(2) = Range(Cells(8, 66), Cells(rowNumber, 66))
With Worksheets("Sheet1")
.ChartObjects(1).Activate
.ChartObjects(1).Chart.HasTitle = True
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "MY CHART"
.ChartObjects(1).Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = r(0)
ActiveChart.SeriesCollection(1).Values = r(1)
ActiveChart.SeriesCollection(2).XValues = r(0)
ActiveChart.SeriesCollection(2).Values = r(2)
End With