Run-Time Error 1004 Application-Definition or Object-Definition Error

[jparnold]

I can't figure it out, This ran with no problem in my test sheet, but
now it gets this error on the

ActiveChart.SeriesCollection(1).XValues = _
"=Team Financial Tables!" & StartCategoryAddress & ":" &
EndCategoryAddress

line. I've triple checked the entire ChartObject section and the
named areas referenced and they seem to be good. The error help is NO
HELP. I have confirned that the variables have the correct values,
but that is where I get stuck. Can someone point me in a good
direction? The entire code is below.

Thanks
Jamie







Sub ResizeChart_Revenue_By_Cust()

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

Sheets("Team Financial Tables").Activate
Set TotalsRange = Sheets("Team Financial Tables").Range
("Revenue_By_Customer")
For Each TotalsCell In TotalsRange

If TotalsCell.Value > 0 And StartTotalsAddress = "" Then
StartTotalsAddress = TotalsCell.Address
(ReferenceStyle:=xlR1C1)

StartCategoryAddress = TotalsCell.Offset(0, -1).Address
(ReferenceStyle:=xlR1C1)

End If
If TotalsCell.Value = 0 Then
EndTotalsAddress = TotalsCell.Offset(-1).Address
(ReferenceStyle:=xlR1C1)

EndCategoryAddress = TotalsCell.Offset(-1, -1).Address
(ReferenceStyle:=xlR1C1)

Exit For
End If
Next

Sheets("Financial Dashboard").Activate
ActiveSheet.ChartObjects("Chart_Revenue_By_Cust").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection(1).XValues = _
"=Team Financial Tables!" & StartCategoryAddress & ":" &
EndCategoryAddress

ActiveChart.SeriesCollection(1).Values = _
"=Team Financial Tables!" & StartTotalsAddress & ":" &
EndTotalsAddress

End Sub

 

[Dave Peterson]

Sometimes, the worksheet name has to be surrounded with apostrophes/single
quotes.

Try
... "='Team Financial Tables'!" & ...

 

[jparnold]

I tryed that and the error moved to the second statement. Thats some
progress ;-). But I got the same error. This does not make a lot of
sense.

Jamie

 

[Dave Peterson]

That assigment depends on what other parts of your code found.

Maybe you should check to see what the variables are before that line executes:

Msgbox StartCategoryAddress & vblf & EndCategoryAddress

ActiveChart.SeriesCollection(1).XValues = _
....

===
Or you could try assigning a valid range address:

ActiveChart.SeriesCollection(1).XValues _
= "='Team Financial Tables!'a1:a9"

(Choose the address that makes sense for your chart.)

At least then you'll have some idea what to work on next.

 

[Barb Reinhardt]

Dave,

IIRC, when I did chart related things (which I haven't done often), I had to
ensure that the addresses were in R1C1 notation. I wonder if that's the
issue.

 

[Dave Peterson]

That sounds like it could be the problem. (I do my best to stay away from
charts! <vbg>)

But I do try to stay away from using strings and let excel do it for me.

ActiveChart.SeriesCollection(1).XValues _
= worksheets("Team Financial Tables").range(StartCategoryAddress, _
EndCategoryAddress)

I don't have to remember any of the syntax.