Add multiple series to chart with loop function

theFE

New Member
Joined
Apr 27, 2018
Messages
9
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!

Code:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

crazydragon84

Board Regular
Joined
Feb 19, 2015
Messages
195
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

New Member
Joined
Apr 27, 2018
Messages
9
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

Board Regular
Joined
Feb 19, 2015
Messages
195
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:

Code:
[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:

Code:
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

New Member
Joined
Apr 27, 2018
Messages
9
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
Rich (BB code):
ActiveChart.SeriesCollection(sn).XValues = ActiveSheet.Range("B" & sn * 107 + 2 & ":B" & (sn+1) * 107 + 1)
Any ideas?
I appreciate the help
 

theFE

New Member
Joined
Apr 27, 2018
Messages
9
SN still zero in the break and error is invalid parameter
 

theFE

New Member
Joined
Apr 27, 2018
Messages
9
I changed sn = 1 and then the break comes at sn = 30 but plot points look like expected so getting somewhere
 

crazydragon84

Board Regular
Joined
Feb 19, 2015
Messages
195
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,477
Messages
5,444,714
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top