1

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"

Community
  • 1
  • 1
NolsN
  • 25
  • 4
  • Try `.Values = "'" & cnGrafer.Name & "'!" & cnGrafer.Range("B5", cnGrafer.Range("b5").Offset(tsEURLengde)).Address` – BigBen Feb 24 '22 at 20:53
  • thanks. I did it but got the same error code: cnGrafer.ChartObjects("chSpreader").SeriesCollection("Bank 2 5y").Values = "'" & cnGrafer.Name & "'!" & cnGrafer.Range("B5", cnGrafer.Range("b5").Offset(tsEURLengde)).Address can it be a References issue? – NolsN Feb 24 '22 at 21:04
  • `.ChartObjects("chSpreader").Chart.SeriesCollection` IIRC ... add the `.Chart` in before `SeriesCollection`. – BigBen Feb 24 '22 at 21:05

1 Answers1

0

Two issues:

.ChartObjects("chSpreader").Chart.SeriesCollection...
  • Rather than assign the 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
BigBen
  • 46,229
  • 7
  • 24
  • 40