Add multiple series to chart with loop function

[theFE]

Hi,
I have been struggling to add a loop function to add 107 series to a chart without manually adding in each one

The X and Y data are in Columns B2:B8882 and C2:8882 and each series I want to plot is 107 data points. Total 83 series.
Below is the code I have so far. it seems to work..ish but extremely slow. Does anybody know what I am doing wrong?
Any Help would be much appreciated!

Sub Plot()
'
' Plot Macro
'
'
' Keyboard Shortcut: Ctrl+Shift+P
'
'




Dim series$, sn%, counter As Long, lastrow As Long
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.ApplyChartTemplate ( _
    "C:\Users\fe\AppData\Roaming\Microsoft\Templates\Charts\reach.crtx")


lastrow = Cells(Rows.Count, "B").End(xlUp).Row
counter = 0


Do Until Cells(2, 2).Offset(107, 0 * counter) = ""
    sn = ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B2:B" & lastrow).Offset(107, 0 * counter)
    ActiveChart.SeriesCollection(sn).Values = ActiveSheet.Range("C2:C" & lastrow).Offset(107, 0 * counter)
    counter = counter + 1
Loop


End Sub

First time posting here so hope I did it correctly

 

[theFE]

Thanks for your help. I will hopefully figure it out and post if I do Much appreciated.
Cell data is good and no blanks. your +2s and + 1s are in the right place yes.
Very eloquent code by the way, nice and neat.
I didn't understand comment though about "make sure for each series your first data point and last data points are using the last range"

 

[crazydragon84]

i was just referring to if my +2 and +1s were referencing the correct first row and last row of the data for each series, which seems like you've confirmed. sorry not sure why it would all the sudden break at 30 if the data looks good though... will check on monday if i remember but i think someone else will give you an answer quicker.

 

[crazydragon84]

Btw just realized, because we changed the initial sn = 0 to 1, you are skipping the very first series. so you need to change all sn * 107 to (sn-1) * 107 and sn-1 to sn-2. also change the loop until sn = 83 to 84.

 

[theFE]

Thanks for your help anyway. I think the +2 and +1 are correct but data collection starts from cell 109 instead of 2.
No breaks in data rows with txt or blanks so I am a bit puzzled why it keeps breaking at 29.
I did not understand your comment "make sure for each series your first data point and last data points are using the last range" Series plotted are following on from previous for the first 29 so looks good.
Very strange, but if I figure it out I will post again. The help thus far has been very much appreciated as the lines you gave me were very eloquent and will be very useful in future.

 

[theFE]

@ Crazydragon84. I changed the code to this (below), sn-1 or -2 giving incorrect end range. It plots perfectly now up until cells 2997 (sn breaking at 29). No idea what is stopping it going past this.

Sub Plot()
Dim sn As Byte
sn = 1


Do Until sn = 84
    ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B" & (sn - 1) * 107 + 2 & ":B" & sn * 107 + 1)
    ActiveChart.SeriesCollection(sn).Values = ActiveSheet.Range("C" & (sn - 1) * 107 + 2 & ":C" & sn * 107 + 1)
    sn = sn + 1
Loop




End Sub

 

[crazydragon84]

did you figure anything else after this? try manually plotting the series on the same graph that macro did. do you have any issues plotting it? I wouldn't think there is, but maybe there an upper limit to how many series you can have in the graph? maybe you didn't have enough series to begin with (did the graph have 83 series to begin with?) if not, maybe you need to "add" more series. add something like this before the activechart.seriescollection line

If ActiveChart.SeriesCollection.Count > sn Then ActiveChart.Seriescollection.add

 

[theFE]

@crazydragon84. my graph plotted fine but there must be an upper limit to number of series with data points so I reduced the number of series by increasing the number of data points assigned to each series. this worked fine for what i was trying to show and also reduced the time it took to actually run the script to plot. Sop thanks for your help, much appreciated.