Chart SeriesCollection error when selecting X ranges in a loop

[aochan]

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!!

 

[pbornemeier]

I believe scatter plots require numeric (or empty -- which will be taken as zero) cells in their Value & XValue ranges. Is it possible that there is a space or other non-numeric value in the failing range?

If you calculate the last data row with something like:
lastrow = cells(rows.count,3).end(xlup).row '3 causes it to find the last populated cell in column C. 4 would find the last populated cell in column D, etc.
That would eliminate many blank cells in the data ranges.