Creating charts with multiple data series (in a loop)

Ealglez

New Member
Joined
Mar 13, 2015
Messages
28
Dear All,

I am struggling trying to set up a code in VBA to create a chart in the active w/s. This chart should be a scatter line with no markers.
The x values are constant for all the series (column A from A19 - the end will be dynamic).
The y values will vary. The number of series will also be dynamic and all the y values are in columns F19 to end (dynamic), K19... i.e. with an offset of 5 columns.

I have crated the following code but it looks like it overlap the previous series.

Can anyone help please? I am new in VBA and I would really appreciate any input.

Also please not that the name of the series refers to different cells in the w/s (starting in C4 and following with D4, E4, etc.




Code:
Sub inhold()
'Charts of Surge and slug volumes vs. time
                                
                                
Dim lastrow As Long
Dim series As String
Dim counter As Long
Dim s

lastrow = Cells(Rows.Count, "A").End(xlUp).Row


ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select

counter = 0
Do Until Cells(19, 6).OFFSET(0, 5 * counter) = ""

    Set s = ActiveChart.SeriesCollection.NewSeries()
    With s
        series = "for" & " " & Cells(4, 3).OFFSET(0, 1 * counter)
        ActiveChart.FullSeriesCollection(1).Name = series
        ActiveChart.FullSeriesCollection(1).XValues = ActiveSheet.Range("A19:A" & lastrow)
        ActiveChart.FullSeriesCollection(1).Values = ActiveSheet.Range("F19:F" & lastrow).OFFSET(0, 5 * counter)

    counter = counter + 1
    End With
   
Loop


End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Try this:

Code:
Sub inhold()
'Charts of Surge and slug volumes vs. time
Dim lastrow As Long, series$, sn%, counter As Long, s, co As ChartObject


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set co = ActiveSheet.ChartObjects.Add(Left:=Cells(2, 9).Left, Width:=Range("b2:m2").Width, _
Top:=Cells(4, 2).Top, Height:=Range("b2:b18").Height)
counter = 0
co.Chart.ChartType = xlXYScatterLinesNoMarkers
Do Until Cells(19, 6).Offset(0, 5 * counter) = ""
    Set s = co.Chart.SeriesCollection.NewSeries()
    sn = co.Chart.SeriesCollection.Count
    series = "for" & " " & Cells(4, 3).Offset(0, 1 * counter)
    co.Chart.SeriesCollection(sn).Name = series
    co.Chart.SeriesCollection(sn).XValues = ActiveSheet.Range("A19:A" & lastrow)
    co.Chart.SeriesCollection(sn).Values = ActiveSheet.Range("F19:F" & lastrow).Offset(0, 5 * counter)
    counter = counter + 1
Loop
End Sub
 

Ealglez

New Member
Joined
Mar 13, 2015
Messages
28

ADVERTISEMENT

Hello again...

I was hoping you can help me out in a silly question...

I have a loop calculation for each of the cases in a list (which are in a different worksheet). The code starts like this:
Code:
Dim counter As Long
 Dim Mycell As Range, myrange As Range
 Set myrange = Sheets("Cases_Input").Range("A4")
 Set myrange = Range(myrange, myrange.End(xlDown))
 
 With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
 
 counter = 0
 For Each Mycell In myrange




But I have discovered that when there is only one case in the list, i.e. when only A4 is populated, the loop goes crazy and does not stop - it cannot find the last cell.
Any idea how can I modify this so that the loop will work even is the list of cases consists only in ONE case??

Thanks a lot!
E
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try replacing...

Code:
 Set myrange = Sheets("Cases_Input").Range("A4")
 Set myrange = Range(myrange, myrange.End(xlDown))

with

Code:
With Sheets("Cases_Input")
    Set myrange = .Range("A4:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

Hope this helps!
 

Ealglez

New Member
Joined
Mar 13, 2015
Messages
28

ADVERTISEMENT

It worked!!

Thank you so so much!
 

Ealglez

New Member
Joined
Mar 13, 2015
Messages
28
Hello... me again :S

OK, I have only one more questions - as it looks like my code is not string enough!
I have the following code to reset a calculation:
Code:
Sub Reset_Calculation()
' Reset calculation
    Dim n As Long
    n = ActiveWorkbook.Worksheets.Count
    If n > 9 Then
    
    With Sheets("Cases_Input")
    Set myrange = .Range("A4:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
     For Each myCell In Sheets("Cases_Input").Range(myrange, myrange.End(xlDown))
    ' Delete cases sheets
     Application.DisplayAlerts = False
     Sheets(myCell.Value).Delete
     Application.DisplayAlerts = True
     Next myCell
     End If
     
     'Delete Out Graphs sheet
     Dim ws As Worksheet
     For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Output_Charts" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        End If
     Next

The problem is the first statement: If n > 9 Then
As n can varies depending on the user etc.
What I need to do is to delete is:
a) ANY w/s whose name is equal to ANY of the names on range "A4:A" (this will be a list of names, as mentioned to you in my previous question)
b) The w/s named Output_Charts (this one can be there or not, depending in what the user has done.

Could you please help me out in re-writing this piece of code, so it will work whatever the number of w/s in the workbook?

(I would understand if I have taken too much of your time though)

Thank you again for all your time,
E
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Try something like this:

Code:
Sub Deleting()
Dim i%
Application.DisplayAlerts = False
On Error Resume Next
For i = 4 To Sheets("Cases_Input").Range("a" & Rows.Count).End(xlUp).Row
    Sheets(Sheets("Cases_Input").Cells(i, 1).Value).Delete
Next
Sheets("Output_Charts").Delete
On Error GoTo 0
Application.DisplayAlerts = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,973
Messages
5,599,124
Members
414,291
Latest member
yanqing06

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
Top