Search code examples
excelvbaexcel-charts

Change chart series values with cell values

I want to use the cell values e.g. (Range(Cells(25, 3), Cells(25, iMonths)) instead of $C$25:$N$25 for the chart's series values as I'm needing to use a variable that changes depending on the date. Is there a way of doing this?

    Dim dtToday As Date
    Dim dtStartDate As Date
    Dim iMonths As Integer
    dtToday = Date
    dtStartDate = "01/01/2021"
    iMonths = DateDiff("m", dtStartDate, dtToday)

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.FullSeriesCollection(6).Name = "='Sheet 1'!$C$25:$N$25"
Solution

  • One way -

    Dim cht As Chart
Dim sr As Series
Dim splt() As String

    Set cht = ActiveSheet.ChartObjects("Chart 2").Chart
    Set sr = cht.SeriesCollection(6)
    splt = Split(sr.Formula, ",")
    splt(2) = "'" & ActiveSheet.Name & "'!" & Range(Cells(25, 3), Cells(25, iMonths)).Address
    sr.Formula = Join(splt, ",")