Plot two series collections in a chart vith VBA

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top