Changing a chart source data in code.

[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?

 

[kkknie]

Charts are a pain, but it looks like you've got a little bug:

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

should read (I believe)

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

It was evaluating as

='FinancialTracker'!R13C2: & strCell

rather than

='FinancialTracker'!R13C2:R20C2 (or whatever you row/colums were.