I am trying to update charts using VBA.
I have a timeseries that I update ad-hoc. I wish to update the chart using VBA. When I get to the chartobjects it errors.
Both the chart and the series have names:
Dim tsEURLengde As Integer
Dim rngEUR As Range
tsEURLengde = Range("A2").Value 'A2 is count of rows containing chartdata
Set rngEUR = cnGrafer.Range("B5", cnGrafer.Range("b5").Offset(tsEURLengde)) 'cnGrafer is the ws codename
cnGrafer.ChartObjects("chSpreader").SeriesCollection("Bank 2 5y").Values = cnGrafer.Range("B5", cnGrafer.Range("b5").Offset(tsEURLengde))
The error message is:
"Run-time error 438: Object doesn't support this property or method"
Two issues:
Chart.SeriesCollection
, not ChartObject.SeriesCollection
..ChartObjects("chSpreader").Chart.SeriesCollection...
Range
, assign its .Address
, including the sheet name:With cnGrafer
Dim s As String
s = "'" & .Name & "'!" & .Range("B5",.Range("b5").Offset(tsEURLengde)).Address
.ChartObjects("chSpreader").Chart.SeriesCollection("Bank 2 5y").Values = s
End With