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

[jparnold]

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

 

[joel]

You have variable in a string and the variable will not be replace wit
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

 

[jparnold]

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

 

[joel]

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

.Address(ReferenceStyle:=xlR1C1

 

[jparnold]

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