Hi everybody, I met a problem when creating multiple series in one chart using a macro. All the series have the same range for X axis but different range for Y axis. The number of series is a variable, and I tried to define the X and Y axis ranges for each series respectively using a loop.
Currently there're 12 series to be plotted. When I run the macro only the first 9 series can be plotted successfully, then it crashes while creating the 10th series, saying "error 1004: invalid parameter".
Here's the code:
Dim i, A, startrow as Integer
Set chart1 = Charts.Add
With chart1
'create and insert the chart as a new sheet after the first sheet
.ChartType = xlXYScatterLinesNoMarkers
.Location where:=xlLocationAsNewSheet, Name:="overall"
.Move after:=Sheets(2)
'set source data for each series
A = 12
startrow = 75
For i = 1 To A
.SeriesCollection(i).XValues = Range(Sheets(1).Cells(startrow + 1, 3), Sheets(1).Cells(startrow + 1000, 3)
.SeriesCollection(i).Values = Range(Sheets(1).Cells(startrow + 1, 12 + A + i), Sheets(1).Cells(startrow + 1000, 12 + A + i))
Next i
End With
I've run the macro several times, every time it stopped while doing the 10th loop of ".SeriesCollection(i).XValues = Range(Sheets(1).Cells(startrow + 1, 3), Sheets(1).Cells(startrow + 1000, 3)" with a error 1004 of invalid parameter.
I'm not sure if this is because there're some empty cells in the designated range for both the X and Y axis (I made the range larger because new data will come in for many times later and I hope the chart can automatically update with the new data). However, since it worked fine for the first 9 series, I doubt the empty cells is the cause of the error.
Has anybody encountered similar issues? Any comments or suggestions will be appreciated, thanks in advance!!
Bookmarks