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

JimPNicholls wrote:

> 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, Microsoft MVP - Excel
http://www.andypope.info

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?






>-----Original Message-----
>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 wrote:
>
>> 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, Microsoft MVP - Excel
>http://www.andypope.info
>.
>

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


JimPNicholls wrote:

> 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?
>
>
>
>
>
>
>
>>-----Original Message-----
>>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 wrote:
>>
>>
>>>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, Microsoft MVP - Excel
>>http://www.andypope.info
>>.
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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/
_______

JimPNicholls wrote:

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