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"
CodePudding user response:
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, ",")