VBA error: Unable to set the Values property of the Series class
 

Excel 2003 SP1

Trying to create a chart using VBA. The following two subs are edited a bit
after being created by the macro recorder.

The send sub errs on this line:
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"

With the error:
Run-time error '1004':
Unable to set the Values property of the Series class

Column 3 is just a series of numbers. Strangely, the first
ActiveChart.SeriesCollection(1).Values line works OK, but then the 2nd
fails.

I can't figure it out...

Sub area_chart()
'
' area_chart Macro
'

'
Range("A1:C72").Select
Charts.Add
ActiveChart.ChartType = xl3DAreaStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C72"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R72C1"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Parent.Name = "Area Chart"
End Sub

Sub area_chart2()
'
' area_chart2 Macro
'

'
ActiveSheet.ChartObjects("Area Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C3:R11C3"
ActiveChart.SeriesCollection(1).Name = "=""CRITICAL"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"
ActiveChart.SeriesCollection(2).Name = "=""MAJOR"""
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R23C3:R33C3"
ActiveChart.SeriesCollection(3).Name = "=""MINOR"""
ActiveChart.SeriesCollection(4).Values = "=Sheet1!R34C3:R44C3"
ActiveChart.SeriesCollection(4).Name = "=""NORMAL"""
ActiveChart.SeriesCollection(5).Values = "=Sheet1!R45C3:R55C3"
ActiveChart.SeriesCollection(5).Name = "=""UNKNOWN"""
ActiveChart.SeriesCollection(6).Values = "=Sheet1!R56C3:R66C3"
ActiveChart.SeriesCollection(6).Name = "=""WARNING"""
Windows("vpo_report.xls").LargeScroll Down:=-1
End Sub

Your macro runs on my computer with a minor change

when I ran it originally, first the area then the Area 2 I got to Series
collection 4 before I got an error message
I changed your initial macro so that it would generate 6 data sets then it
ran correct
it seems as though the new series generation works but doesn't like to
modify the values (I don't know why, there may be somehing in the format I
do not recognize yet)

"Marco Shaw" wrote:

Excel 2003 SP1

Trying to create a chart using VBA. The following two subs are edited a bit
after being created by the macro recorder.

The send sub errs on this line:
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"

With the error:
Run-time error '1004':
Unable to set the Values property of the Series class

Column 3 is just a series of numbers. Strangely, the first
ActiveChart.SeriesCollection(1).Values line works OK, but then the 2nd
fails.

I can't figure it out...

Sub area_chart()
'
' area_chart Macro
'

'
Range("A1:C72").Select
Charts.Add
ActiveChart.ChartType = xl3DAreaStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C72"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R72C1"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Parent.Name = "Area Chart"
End Sub

Sub area_chart2()
'
' area_chart2 Macro
'

'
ActiveSheet.ChartObjects("Area Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C3:R11C3"
ActiveChart.SeriesCollection(1).Name = "=""CRITICAL"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"
ActiveChart.SeriesCollection(2).Name = "=""MAJOR"""
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R23C3:R33C3"
ActiveChart.SeriesCollection(3).Name = "=""MINOR"""
ActiveChart.SeriesCollection(4).Values = "=Sheet1!R34C3:R44C3"
ActiveChart.SeriesCollection(4).Name = "=""NORMAL"""
ActiveChart.SeriesCollection(5).Values = "=Sheet1!R45C3:R55C3"
ActiveChart.SeriesCollection(5).Name = "=""UNKNOWN"""
ActiveChart.SeriesCollection(6).Values = "=Sheet1!R56C3:R66C3"
ActiveChart.SeriesCollection(6).Name = "=""WARNING"""
Windows("vpo_report.xls").LargeScroll Down:=-1
End Sub