CODEWITHSUNDEEP
excelvba
Andrew Pettit
Andrew Pettit

Reputation: 37

Creating a scatter plot with multiple series in VBA

Creating a Scatter Plot using VBA and i have multiple XY series that i want to use.

Im using seriescollect and the program works with just one set of data and i try to use more than on and i get an error message.

Private Sub generate_scatterplot()

Dim ochartObj As ChartObject
Dim oChart As Chart

Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set oChart = ochartObj.Chart

With oChart
    oChart.ChartType = xlXYScatterSmooth
    oChart.SeriesCollection.NewSeries
    oChart.SeriesCollection(1).XValues = Range("O200:S200")
    oChart.SeriesCollection(1).Values = Range("O201:S201")
    oChart.SeriesCollection(2).XValues = Range("O202:S202")
    oChart.SeriesCollection(2).Values = Range("O203:S203")
End With
End Sub

With one set of data output is what i want just need to change some titles, but when i try to have more than one series i get an error message that says \

"Run-Time error 1004: Application-defined or object-defined error"

Upvotes: 1

Views: 4538

Answers (1)

Domenic
Domenic

Reputation: 8114

While you initially created a series and assigned it values, you've neglected to create a new one for your second series. By the way, since you're using a With/End With statement to refer to your chart, you can start your lines with the dot (.) operator. Try the following...

Private Sub generate_scatterplot()

    Dim oChartObj As ChartObject
    Dim oChart As Chart
    Dim rSourceData As Range
    Dim i As Long

    Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
    Set oChart = oChartObj.Chart

    Set rSourceData = Range("O200:S249")

    With oChart
        .ChartType = xlXYScatterSmooth
        For i = 1 To rSourceData.Rows.Count Step 2
            With .SeriesCollection.NewSeries
                .XValues = rSourceData.Rows(i)
                .Values = rSourceData.Rows(i + 1)
            End With
        Next i
    End With

End Sub

Hope this helps!

Upvotes: 3

Related Questions