Changing Chart SourceData in VBA

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

 

[Andy Pope]

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

 

[JimPNicholls]

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?

 

[Andy Pope]

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

 

[Jon Peltier]

Jim -

You can also do this without VBA, using dynamic ranges. I have a few
examples and a lot of links:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______