replace chart series values vba

Question

Hello,

this creates a new series on a chart, the array feeding it is myarr(11,12). each column on the array is selected using index.

The problem I have is that everytime I select a new column (with i as variable), a new series is created.

I just want to able to replace the values in the existing  series without drawing new series*.*

Thanks

With Worksheets("Charts").ChartObjects("myChart").Chart.SeriesCollection.NewSeries

            .Values = Application.Index( myarr, 0, i )

            .XValues = Array(2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)

            .Name = "Sun"           

End With

Answer 1

Instead of

With Worksheets("Charts").ChartObjects("myChart").Chart.SeriesCollection.NewSeries

use

With Worksheets("Charts").ChartObjects("myChart").Chart.SeriesCollection(1)

if you want to change the first chart series.

Answer 2

If you have already created the series, it has become the last series in the chart, so you can use

    With Worksheets("Charts").ChartObjects("myChart").Chart

        With .SeriesCollection(.SeriesCollection.Count)

            .Values = Application.Index( myarr, 0, i )

            .XValues = Array(2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)

            .Name = "Sun"          

        End With

    End With

Answer 3

thanks.

have another question, if I was to create a NewSeries first and then change its values (Y), what  would be the best approach?