Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
 

Next Problem is I now get a Run-Time Error 1004 on
ActiveChart.SeriesCollection part of my program. I double checked the
variable names and everything is consistant. It appears that I can
not use a varible name in the .Values and .XValues statements. The
error code explaination is not very helpful. My code is below.
Again thanks for your help.

Sub CreateNewSortRange()

Dim TotalsRange As Range
Dim TotalsCell As Range

Dim CategoryRange As Range
Dim CategoryCell As Range

Dim StartTotalsAddress As String
Dim EndTotalsAddress As String

Dim StartCategoryAddress As String
Dim EndCategoryAddress As String


Set TotalsRange = Sheets("TestRange").Range("C5:C24")
For Each TotalsCell In TotalsRange

If TotalsCell.Value 0 And StartTotalsAddress = "" Then
StartTotalsAddress = TotalsCell.Address
StartCategoryAddress = TotalsCell.Offset(0, -1).Address
End If
If TotalsCell.Value = 0 Then
EndTotalsAddress = TotalsCell.Offset(-1).Address
EndCategoryAddress = TotalsCell.Offset(-1, -1).Address
Exit For
End If
Next


ActiveSheet.ChartObjects("TotalsChart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=TestRange!
StartTotalsAddress:EndTotalsAddress"
ActiveChart.SeriesCollection(1).Values = "=TestRange!
StartCategoryAddress:EndCategoryAddress"


End Sub

Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
 

You have variable in a string and the variable will not be replace with
numbers. You have to seperate the string as shown below

ActiveChart.SeriesCollection(1).XValues = _
"=TestRange!" & StartTotalsAddress & ":" & EndTotalsAddress


Not surere why you have the line listed twice


I sometime do this a little different

set StartCategory = TotalsCell.Offset(0, -1)
set EndCategory = TotalsCell.Offset(-1, -1)

Set DataRange = Range(StartCategory,EndCategory)

ActiveChart.SeriesCollection(1).XValues = _
"=" & DataRange.address(External:=True)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164872

Microsoft Office Help

Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
 

I still get the same error. Below is the revised code as per your
suggestion:

Sub CreateNewSortRange()

Dim TotalsRange As Range
Dim TotalsCell As Range

Dim CategoryRange As Range
Dim CategoryCell As Range

Dim StartTotalsAddress As String
Dim EndTotalsAddress As String

Dim StartCategoryAddress As String
Dim EndCategoryAddress As String


Set TotalsRange = Sheets("TestRange").Range("C5:C24")
For Each TotalsCell In TotalsRange

If TotalsCell.Value 0 And StartTotalsAddress = "" Then
StartTotalsAddress = TotalsCell.Address
StartCategoryAddress = TotalsCell.Offset(0, -1).Address
End If
If TotalsCell.Value = 0 Then
EndTotalsAddress = TotalsCell.Offset(-1).Address
EndCategoryAddress = TotalsCell.Offset(-1, -1).Address
Exit For
End If
Next

ActiveSheet.ChartObjects("TotalsChart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=TestRange!" &
StartTotalsAddress & ":" & EndTotalsAddress
ActiveChart.SeriesCollection(1).Values = "=TestRange!" &
StartCategoryAddress & ":" & EndCategoryAddress













On Dec 23, 5:18*pm, joel wrote:
You have variable in a string and the variable will not be replace with
numbers. *You have to seperate the string as shown below

ActiveChart.SeriesCollection(1).XValues = _
"=TestRange!" & StartTotalsAddress & ":" & EndTotalsAddress

Not surere why you have the line listed twice

I sometime do this a little different

set StartCategory = TotalsCell.Offset(0, -1)
set EndCategory = TotalsCell.Offset(-1, -1)

Set DataRange = Range(StartCategory,EndCategory)

ActiveChart.SeriesCollection(1).XValues = _
"=" & DataRange.address(External:=True)

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=164872

Microsoft Office Help

Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
 

the address needs to be in R1C1 format change you r address properties
to look like this in all places

.Address(ReferenceStyle:=xlR1C1)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164872

Microsoft Office Help

Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
 

That's IT!!! Thank you SO much for your help!!! Jamie



On Dec 24, 6:29*am, joel wrote:
the address needs to be in R1C1 format change you r address properties
to look like this in all places

.Address(ReferenceStyle:=xlR1C1)

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=164872

Microsoft Office Help