Error 1004 - Axisgroup property problem with chart macro

Hello everyone.

The problem I have is with my macro for a chart.

I use data from worksheet "Graph Data" and plot the chart (two y-axes) on the worksheet "Graph".

My data include 0 values but I've tested different datasets (with and without values) and I keep getting the same message:

Run-Time error '1004':
Unable to set the AxisGroup property of the Series class

The macro breaks at row 64 (rows 64 and 64 have 0 values but so do previous rows) with that message. On debugging the macro I see that the culprit is the:

Please Login or Register  to view this content.

bits of code.

I attach the relevant spreadsheets with the macro. Any help will be greatly appreciated.

Please keep in mind that the number of rows is dynamic and I have that accounted by using another piece of code.

If you change the following line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

everything works fine. As soon as it goes over row 63 it breaks down.

Any ideas will be greatly appreciated.

The problem is you are creating a custom line chart with 2 axis.
This places series automatically onto the secondary axis.

As you chart only has 2 series moving 1 to the secondary axis will fail.

To make the code more easily identify you data remove the heading from 'Graph Data'!A4

Remove the line that set the line chart type and instead you this,

Please Login or Register  to view this content.

Notice I have also move the 2 lines that add secondary axis titles. Those bits of code can be moved to the end of the routine. As where they were would fail as no data in on the secondary axis.

It works!

Thank you _so_ much! I've spent a lot of time googling for that error and I kept getting pages about a "bug" of excel to handle the axisgroup moves - but nothing worked!

It works great now.

Could you please clarify why you mention that I have only two series? I actually have 7 used in my chart (I think I didn't understand correctly what you mean by 2 series).


Regards,

Kostas

I mean you chart only has 2 series when created with that code.

In your example file go to the Graph Data sheet.
Select the range $A$4:$H$71
Create a line chart.

Now check how many data series you have. When I plot by Columns i have 2 series called.

Line Average #NUM! #NUM! etc

and

Schedule Dwell 00:0027 00:00:00 etc

Originally Posted by Andy Pope

I mean you chart only has 2 series when created with that code.

In your example file go to the Graph Data sheet.
Select the range $A$4:$H$71
Create a line chart.

Now check how many data series you have. When I plot by Columns i have 2 series called.

Line Average #NUM! #NUM! etc

and

Schedule Dwell 00:0027 00:00:00 etc

Ok, I see what you mean now. Thank you.