Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
ActiveChart.SeriesCollection(1).Points(3).Select ERROR | Excel Programming | |||
Obtaining the value of an ActiveChart.SeriesCollection(1).Points(2) | Excel Programming | |||
Runtime Error '1004' Method 'ActiveChart' of Object '_Global' failed | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |