Excel - Charts - Run-time error '1004' 1

[verplexd]

Hi all,

I recorded a macro to create a two y-axis chart. When I tried to assign the code to a command button and to create the chart I'm getting:

Run-time error '1004'
Method 'Axis' of object '_Chart' failed

The last few lines cause the error. Here's the code:




Sub CreateChart()

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("SpeedTable" ).Range("G19" ), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=SpeedTable!R3C1:R508C1"
ActiveChart.SeriesCollection(1).Values = "=SpeedTable!R3C2:R508C2"
ActiveChart.SeriesCollection(1).name = "=""Speed"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=SpeedTable!R3C4:R508C4"
ActiveChart.SeriesCollection(2).name = "=""Gear"""
ActiveChart.Location Where:=xlLocationAsObject, name:="Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Speed/Shift Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time [sec]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Speed [kph]"
.Axes(xlCategory, xlSecondary).HasTitle = False 'causes the error
.Axes(xlValue, xlSecondary).HasTitle = True 'causes the error
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Gear" 'causes the error
End With
End Sub



Anyone have any suggestions?

Thanks,
Jason

 

[xlbo]

Well - a 1004 error usually means you are trying to refer to something that isn't there
In thios case, the errors seem to occur when you reference your secondary axis - my guess would be that your new data doesn't have a secondary axis and therefore the code is bombing....

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[verplexd]

Actually I was able to get it working with this code:



Sub CreateChart()

Dim SpeedChart As Chart

Application.ScreenUpdating = False

Sheet3.ChartObjects.Delete

Set SpeedChart = Charts.Add
Set SpeedChart = SpeedChart.Location(Where:=xlLocationAsObject, name:="Graph" )

With SpeedChart
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
.SetSourceData Source:=Sheets("SpeedTable" ).Range("G19" ), PlotBy _
:=xlColumns
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=SpeedTable!R3C1:R508C1"
.SeriesCollection(1).Values = "=SpeedTable!R3C2:R508C2"
.SeriesCollection(1).name = "=""Speed"""
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = "=SpeedTable!R3C4:R508C4"
.SeriesCollection(2).name = "=""Gear"""
End With

'Adding the lables for the two y-axis
SpeedChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
SpeedChart.Axes(xlValue, xlSecondary).HasTitle = True
SpeedChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Gear"
SpeedChart.Axes(xlCategory, xlPrimary).HasTitle = True
SpeedChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time [sec]"
SpeedChart.Axes(xlValue, xlPrimary).HasTitle = True
SpeedChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Speed [kph]"
End Sub



Maybe it was a problem with the "With" statement.
It works fine now.

 

[xlbo]

Actually, I think this is what has made the difference:
SpeedChart.Axes(xlValue, xlSecondary).HasTitle = True
as opposed to:
.Axes(xlCategory, xlSecondary).HasTitle = False

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[verplexd]

If that were the case, then this would work:

.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Gear"

But that is not the case.

 

[verplexd]

This works too:

[vb]
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Speed/Shift Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time [sec]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Speed [kph]"
End With
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.Axes(xlCategory, xlSecondary).HasTitle = False
ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Gear"
[/vb]

For some reason I have to redefine the chart type before I can edit the second y-axis. If I take out that line [TypeName:= "Line - Column on 2 Axes"] I get the 1004 error.

 

[verplexd]

I stepped through the code with the debugger and found that the chart type was changing after I set the source data. Therefore this doesn't work:
[vb]
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("SpeedTable" ).Range("G19" ), PlotBy _
:=xlColumns
[/vb]


For some reason it would not let me set the chart type until after I added a my second series:
[vb]
ActiveChart.SeriesCollection(1).name = "=""Speed"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SeriesCollection(2).Values = "=SpeedTable!R3C4:R508C4"
[/vb]


So you were right on from the very begining with your first post xlbo... I was trying to refer to something that wasn't there.

Thanks for your input,
Jason

 

[xlbo]

No worries - glad you got it sorted

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde