Only first or last series created through loop is plotted instead of all

Gnera

New Member
Joined
Nov 19, 2017
Messages
11
The problem I have is already stated in the title - I have a loop generating 18 series in my code and I'd like all of those 18 series to be plotted on one graph. Somehow I don't know how to do it. It's probably a simple and obvious mistake, but I fail to see it. Hopefully someone could point it out.
The relevant part of my code:
Code:
'add a chart
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

'avoid extra series
    Do Until ActiveChart.SeriesCollection.Count = 0
    ActiveChart.SeriesCollection(1).Delete
    Loop
    
'loop through columns
    For i = 1 To 137 Step 8
    j = Cells(30, i).End(xlDown).Row
        If Abs(Cells(30, i)) = 0.2 Or Abs(Cells(30, i)) = 0.3 Then 
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.FullSeriesCollection(1).Name = Cells(12, i + 5)
            ActiveChart.FullSeriesCollection(1).XValues = Range(Cells(31, i), Cells(j, i))
            ActiveChart.FullSeriesCollection(1).Values = Range(Cells(31, i + 1), Cells(j, i + 1))
            Exit For
        Else
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.FullSeriesCollection(1).Name = Cells(12, i + 5)
            ActiveChart.FullSeriesCollection(1).XValues = Range(Cells(30, i), Cells(j, i))
            ActiveChart.FullSeriesCollection(1).Values = Range(Cells(30, i + 1), Cells(j, i + 1))
            Exit For
        End If
    Next
This code only plots the first (i=1) series and the graph's data set has only the 1st series in its data set. If I, however, delete the Exit For's then only the last (i=137) series is plotted and all the others are in the graph's data set but are empty.
Would appreciate any help!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try replacing...

Code:
     ActiveChart.SeriesCollection.NewSeries
     ActiveChart.FullSeriesCollection(1).Name = Cells(12, i + 5)
     ActiveChart.FullSeriesCollection(1).XValues = Range(Cells(31, i), Cells(j, i))
     ActiveChart.FullSeriesCollection(1).Values = Range(Cells(31, i + 1), Cells(j, i + 1))
     Exit For

with

Code:
    With ActiveChart.SeriesCollection.NewSeries
        .Name = Cells(12, i + 5)
        .XValues = Range(Cells(31, i), Cells(j, i))
        .Values = Range(Cells(31, i + 1), Cells(j, i + 1))
    End With

Same thing for your Else statements.

Hope this helps!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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