Error message when adding a name

[matelot]

I get the following errors when trying to add a name to a chart using my macro.
Code copied directly from recorded macro
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Name = "=A1"

run-time error 1004
Application defined or object-defined error.
Why is it not working?

Thanks

 

[Bob Flanagan]

Try this instead:

ActiveChart.SeriesCollection(1).Name = _
"=" & Range("a1").Address(external:=True, _
ReferenceStyle:=xlR1C1)

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[matelot]

Bob,
It works. Do you know why the macro that Excel output from the recorded
macro doesn't work? Just out of curiosity.

Thanks
Mat

 

[Jon Peltier]

The macro recorder isn't perfect. What you need to remember is that the
chart isn't part of the worksheet, even though it may be embedded on it. So
any reference to the sheet has to be explicitly stated. So enter
=Sheet1!$A$1 instead.

In fact, in Excel 2003, the macro recorder recorded this:

ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C1"

- Jon