Changing Chart SourceData in VBA

  • Thread starter Thread starter JimPNicholls
  • Start date Start date
J

JimPNicholls

Excel 97.

Can anyone spot where I'm going wrong? I'm trying to
change the source data in code so it only shows upto the
last cell that has a value in it. My code at the mo is..

Dim intActiveCell As Integer
Dim strCell As String
Dim strrange As String

Sheets("Financial Tracker").Select
Range("C13").Select

If ActiveCell <> "" Then
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
End If
ActiveCell.Offset(0, -1).Select
'Selection.End(xlLeft).Select
strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column


Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Values = "='Financial
Tracker'!R13C2: & strCell"


It works fine until the last line where it falls over
with "Object variable......"message. Can this be done in
code?
 
Hi Jim,

Try revising the last line., looks like the variable strCell is inside
the quotes.

ActiveChart.SeriesCollection(3).Values _
= "='Financial Tracker'!R13C2:" & strCell

Cheers
Andy
 
Andy

Thanks for the quick reply. I've tried that but I'm still
getting the following error message "Run-Time error'1004'
MEthod 'SeriesCollection' of object ' _Chart' failed. Any
ideas?
 
Has the chart actually got 3 data series?

The error appears for me if I try it on a chart with only 2 data series.

Cheers
Andy
 
Back
Top