VBA - Multiple series to a chart
11,537
The code that works.
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim thearray(9) As Double
Dim chrt As Chart
Dim n As Integer, i As Integer, q As Integer
Set chrt = sh.Shapes.AddChart.Chart
For q = 1 To 2
For i = 0 To 9
thearray(i) = WorksheetFunction.RandBetween(1, 20)
Next
With chrt
.ChartType = xlXYScatterLines
.SeriesCollection.NewSeries
.SeriesCollection(q).Name = "HFF " & q
.SeriesCollection(q).XValues = Range("B2:K2")
.SeriesCollection(q).Values = thearray
.SeriesCollection(q).MarkerSize = 4
For n = .SeriesCollection.Count To 3 Step -1
.SeriesCollection(n).Delete
Next n
End With
Next
End Sub
Related videos on Youtube
Author by
docjay
Updated on June 04, 2022Comments
-
docjay almost 2 years
I'm trying to make add two series to a single XYscatter chart via loop. Currently, my code creates two charts. The X values are constant but the Y values change so I added them to an array to preserve them.
Sub test() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Sheet1") Dim thearray(9) As Double Dim chrt As Chart Dim n As Integer, i As Integer, q As Integer For q = 1 To 2 For i = 0 To 9 thearray(i) = WorksheetFunction.RandBetween(1, 20) Next Set chrt = sh.Shapes.AddChart.Chart With chrt .ChartType = xlXYScatterLines .SeriesCollection.NewSeries .SeriesCollection(1).Name = "TEST" .SeriesCollection(1).XValues = Range("B2:K2") .SeriesCollection(1).Values = thearray .SeriesCollection(1).MarkerSize = 4 For n = .SeriesCollection.Count To 2 Step -1 .SeriesCollection(n).Delete Next n End With Next End Sub
I appreciate any help offered.
Edit: I tried changing
.SeriesCollection(1)
To
.SeriesCollection(q)
But it doesn't work.
EDIT2: I figured it out. I took
Set chrt = sh.Shapes.AddChart.Chart
Out of the loop and replaced 1 with q in .SeriesCollection