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
Share:
11,537

Related videos on Youtube

docjay
Author by

docjay

Updated on June 04, 2022

Comments

  • docjay
    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