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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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?
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
I changed sn = 1 and then the break comes at sn = 30 but plot points look like expected so getting somewhere
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top