Plot two series collections in a chart vith VBA

C

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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Chart Series title with multiple cells
Replies
4
Views
130
mikejvir
M
I
  • Question Question
Data formatting for Chart
Replies
3
Views
681
IREALLYambatman
I
T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
205
TheTiredEngineer
T
E
  • Question Question
Trying to put the chart below Sales Value
Replies
2
Views
337
Eric Penfold
E
J
  • Question Question
VBA Clear Chart Dynamically and refresh with new values
2
Replies
10
Views
2K
Jon Peltier
J

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

Share this page

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