CODEWITHSUNDEEP
excelvbaexcel-charts
Mulloy
Mulloy

Reputation: 176

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"

Upvotes: 0

Views: 168

Answers (1)

Peter T
Peter T

Reputation: 316

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, ",")

Upvotes: 1

Related Questions