How to create a loop to SetSourceData on a line chart

Question

I am creating a Monte Carlo style model for forecasting demand for my company. The model runs as many times as the user requests (needs to be dynamic) and sends back how many "batches" we can expect to manufacture in a given year. I want to plot the results.

I want to include one line per simulation on the chart. However, the number of simulations will change depending on the user's input from another tab, which is titled "control panel".

I have tried to create a loop that will "SetSourceData" dynamically but I have been unsuccessful. Failed code below. Any advice on how to select non-contiguous rows in this fashion would be appreciated.

ThisWorkbook.Worksheets("Charts").Activate
NumberSimulations = ThisWorkbook.Sheets("Control Panel").Cells(28, 3)

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
j = 0
For i = 1 To NumberSimulations
    ActiveChart.SetSourceData Source:=Sheets("Batches").Range(Cells(2 + j, 1), Cells(2 + j, 22))
    j = j + 7
Next i

The Table that I need to loop over:

Desired Output:

Does this answer your question? [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — BigBen, Jan 17 '20 at 20:03
You can't use `SetSourceData` incrementally - it sets the input for the whole chart. — Tim Williams, Jan 17 '20 at 20:18

score 1 · Answer 1 · answered Jan 17 '20 at 21:06

You can't use SetSourceData incrementally - it sets the input for the whole chart. something like this, where you add each series individually, would be better/more flexible:

Sub AddAChart()

    Dim co As ChartObject, cht As Chart, NumberSimulations As Long, i As Long
    Dim wsData As Worksheet

    Set wsData = ThisWorkbook.Worksheets("Batches")

    'add a new chart and clear any data which might have been added to it
    '  (eg if cells were selected when it was added)
    Set co = ThisWorkbook.Worksheets("Charts").ChartObjects.Add(100, 100, 300, 300)
    Set cht = co.Chart
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
    Loop
    cht.ChartType = xlLineMarkers

    NumberSimulations = ThisWorkbook.Sheets("Control Panel").Cells(28, 3)

    For i = 1 To NumberSimulations
        'add this series
        With cht.SeriesCollection.NewSeries
            .XValues = wsData.Range("C1:V1")
            .Values = wsData.Range("C1:V1").Offset(1 + ((i - 1) * 7))
        End With
    Next i

End Sub

How to create a loop to SetSourceData on a line chart

1 Answers1