Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA error: Unable to set the Values property of the Series class
An ongoing issue in Excel: you can't programmatically access a series that
has all "#N/A" values, and that is what you get when you add a new series without specifying the data values at the time of creation (see, for example, http://support.microsoft.com/default...;en-us;213379). Solution: instead of NewSeries, use the Add method to create the series and specify the values at the same time, e.g.: ActiveChart.SeriesCollection.Add Source:=Range("C12:C22") ActiveChart.SeriesCollection.Add Source:=Range("C23:C33") ActiveChart.SeriesCollection.Add Source:=Range("C34:C44") ActiveChart.SeriesCollection.Add Source:=Range("C45:C55") ActiveChart.SeriesCollection.Add Source:=Range("C56:C66") However, the number of series you need to add depends on the data range you specified when you first created the chart. If column B also contains numeric values, the chart will have 2 initially filled data series when you create it (cols B and C); but if only column C then you will have only one filled series. Since your error is coming when you try to use SeriesCollection(2), I would guess you only have the one filled series (which is why there was no error when you accessed SeriesCollection(1)). Hope this clarifies things - it is a pretty murky subject, and frankly the AddSeries method doesn't make much sense given the 'bug' ('design feature'?) trying to access the series when it is blank. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA error: Unable to set the Values property of the Series class
Solution: instead of NewSeries, use the Add method to create the series
and specify the values at the same time, e.g.: That did the trick! Thanks. Marco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to set the values property of the series class | Excel Discussion (Misc queries) | |||
Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
VBA error: Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
Unable to set the xvalues (or values) property of the series class | Excel Programming |