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

 

[crazydragon84]

hi. i don't understand your code.

1. not sure what the purpose of counter is... you are multiplying it to zero every time.
2. it looks like you are plotting X and Y axis one by one from B2 to B109.
3. why are you looping through each row? couldn't you just add the series in one shot saying range B2:C109?

 

[theFE]

Hi,
I am trying to plot all the points in B and C as X and y. First data series to run From B2:B109 and C2:C109 for X and Y respectively then next data series pick from B110 and C110 + the next 107 points so on so that I end with 83 data series on the same chart.
1. I though I could use counter to count range from 1:107 then move onto next data series. Maybe I am very wrong here but end goal is to plot 83 separate data series from my list of x's and y's in my B and C column.
Any pointers much appreciated

 

[crazydragon84]

oh i see, you have 9000 rows because you have 83 sets of 107 rows. gotcha.

from what i'm looking at the only thing your code is doing is looping through first 107 lines (and let me know if i'm wrong, but i don't think the loop will ever end). Change this:

[TABLE="width: 56"]
 <colgroup><col width="56" style="width:42pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 56"][TABLE="width: 785"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Do Until Cells(2, 2).Offset(107, 0 * counter) = ""[/TD]
[/TR]
[TR]
[TD]    sn = ActiveChart.SeriesCollection.Count[/TD]
[/TR]
[TR]
[TD]    ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B2:B" & lastrow).Offset(107, 0 * counter)[/TD]
[/TR]
[TR]
[TD]    ActiveChart.SeriesCollection(sn).Values = ActiveSheet.Range("C2:C" & lastrow).Offset(107, 0 * counter)[/TD]
[/TR]
[TR]
[TD]    counter = counter + 1[/TD]
[/TR]
[TR]
[TD]Loop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

to this:

dim sn as byte 
sn = 0

[TABLE="width: 56"]
<colgroup><col width="56" style="width: 42pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 56"][TABLE="width: 785"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Do Until sn  = 83[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]    ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B" & sn * 107 + 2 & ":B" & (sn+1) * 107 + 1)[/TD]
[/TR]
[TR]
[TD]    ActiveChart.SeriesCollection(sn).Values = ActiveSheet.Range("C" & sn * 107 + 2 & ":C" & (sn+1) * 107 + 1)[/TD]
[/TR]
[TR]
[TD]    sn= sn+ 1[/TD]
[/TR]
[TR]
[TD]Loop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I have not tested this code. but let me know if something like that works. you might have to change around the + 2 and + 1 maybe...

 

[theFE]

Hi,
First code would plot points but was itterating for very long times and picking C column past end point.
I have tried your code and unfortunately still getting error at

ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B" & sn * 107 + 2 & ":B" & (sn+1) * 107 + 1)

Any ideas?
I appreciate the help

 

[crazydragon84]

what is sn = in the break? also do you know which part of that line is breaking?

 

[theFE]

SN still zero in the break and error is invalid parameter

 

[crazydragon84]

try changing sn = 0 to 1. sorry i thought plots start at series 0 but i could be wrong.

 

[theFE]

I changed sn = 1 and then the break comes at sn = 30 but plot points look like expected so getting somewhere

 

[crazydragon84]

check your data between rows 3212 and +107 to see if there are any blanks or texts or something. Also make sure for each series your first data point and last data points are using the last range. I think i did my +2s and +1s right but i'm not 100% sure lol i might take off for the week soon so might not reply over the weekend sorry