CODEWITHSUNDEEP
vbafor-loop
J dH
J dH

Reputation: 1

How to create a loop to add data series to a chart?

I would like to create a Macro for Excel 2016 that loops through data adding series to a chart. I need help with creating the loop, at the moment I am manually writing it as my attempts at creating a loop hasn't succeeded. The long-way code looks like this:

Sub Button1_Click()

    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.FullSeriesCollection(1).Name = "='TO DP Compressor 
Maps'!$A$3"
        ActiveChart.FullSeriesCollection(1).XValues = _
        "='TO DP Compressor Maps'!$B$4:$B$23"
        ActiveChart.FullSeriesCollection(1).Values = _
        "='TO DP Compressor Maps'!$C$4:$C$23"

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.FullSeriesCollection(2).Name = "='TO DP Compressor 
Maps'!$D$3"
        ActiveChart.FullSeriesCollection(2).XValues = _
        "='TO DP Compressor Maps'!$E$4:$E$23"
        ActiveChart.FullSeriesCollection(2).Values = _
        "='TO DP Compressor Maps'!$F$4:$F$23"

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.FullSeriesCollection(3).Name = "='TO DP Compressor 
Maps'!$G$3"
        ActiveChart.FullSeriesCollection(3).XValues = _
        "='TO DP Compressor Maps'!$H$4:$H$23"
        ActiveChart.FullSeriesCollection(3).Values = _
        "='TO DP Compressor Maps'!$I$4:$I$23"

End Sub

As you can see, the pattern repeats itself. Can someone help me set up a loop?

Many thanks

Upvotes: 0

Views: 1987

Answers (1)

DisplayName
DisplayName

Reputation: 13386

for instance:

Sub Button1_Click()
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
    Dim i As Long
    With ActiveChart
        For i = 1 To 3
            With .SeriesCollection.NewSeries
                .name = "='TO DP Compressor Maps'!" & Range("$A$3").Offset(, (i - 1) * 3).Address
                .XValues = "='TO DP Compressor Maps'!" & Range("$B$4:$B$23").Offset(, (i - 1) * 3).Address
                .values = "='TO DP Compressor Maps'!" & Range("$B$4:$B$23").Offset(, (i - 1) * 3 + 1).Address
            End With
        Next
    End With
End Sub

Upvotes: 1

Related Questions