vba create multiple series xl3dline chart - error 1004

[SteveBanks]

I have a problem trying to programatically create a 3dLine chart, wher
it gives me a '1004' error when trying to set any properties o
SeriesCollection(2).

I can reproduce the error in its simplest form (ignoring my code b
using the macro recorder to record creating the chart, as I get th
error when I play back the recorded macro. It seems to be a proble
manipulating the second (or more) series of this type of chart. If
set the charttype to xlLineStacked for example, it works fine.

I have attached a crude example in chartdemo.xls, which contains
sub's, one that works with a LineStacked chart and one that fails wit
a 3dLine chart, or the relevant code is pasted below.

I have to get a series from a number of sheets, rather than having th
data all on one sheet and charting it with a large 'setsourcedata
range, which I guess is the fallback plan...

Does anyone have any ideas, or am I cracking up?

Thanks

Steve


Charts.Add
ActiveChart.ChartType = xl3dline
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B13")
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R2C2:R13C2"
ActiveChart.SeriesCollection(2).Name = "=Sheet2!R1C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1

+----------------------------------------------------------------
| Attachment filename: chartdemo.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=356339
+----------------------------------------------------------------

 

[SteveBanks]

(replying to my own post)

Oddly, If I change the code to create it as '3dlinemarkers' or som
other type that works, then change it to 3dline at the end, it work
fine. Go figure...


Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B13")
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R2C2:R13C2"
ActiveChart.SeriesCollection(2).Name = "=Sheet2!R1C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.ChartType = xl3dlin

 

[Jon Peltier]

Steve -

Whatever works, right? Before I saw your second post, I was going to
suggest saving the .ChartType= line until the end.

I was going to point out that the values range and name range you are
using for the added series 2 (R2C2:R13C2 or B2:B13, and R1C2 or B1)) are
included in the source data range (A1:B13). Plotting the same data
twice wouldn't cause any problem, but it's redundant, and you might be
charting something you don't need.

- Jon