Chart error
 

Hi all,

I ensounter a runtime error 1004 'Method 'Axes' of object '_Chart' failed'
when I run this code (that I recorded):

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"
ActiveChart.SetSourceData Source:=Sheets("Gas").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Direct Cost Per Trade"""
ActiveChart.SeriesCollection(2).Name = "=""Total Direct Cost"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Gas Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<=== Causing the
error!!
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct Cost
Per Trade"
End With

I'm a novice with VBA and have never used code and charts before so have no
idea why this is happening.

The chart is a line graph with both Y Value axes required - one for each
line ('series').

Chart error
 

The error was with the order in which the chart type was identified. Solution:

Charts.Add

ActiveChart.SetSourceData Source:=Sheets("Gas").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Total Direct Cost"""
ActiveChart.SeriesCollection(2).Name = "=""Direct Cost Per Trade"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Gas Charts"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes" '<= after chart has data, not after 'Charts.Add'

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct Cost
Per Trade"
End With

"ewan7279" wrote:

Hi all,

I ensounter a runtime error 1004 'Method 'Axes' of object '_Chart' failed'
when I run this code (that I recorded):

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes"
ActiveChart.SetSourceData Source:=Sheets("Gas").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Direct Cost Per Trade"""
ActiveChart.SeriesCollection(2).Name = "=""Total Direct Cost"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Gas Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<=== Causing the
error!!
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct Cost
Per Trade"
End With

I'm a novice with VBA and have never used code and charts before so have no
idea why this is happening.

The chart is a line graph with both Y Value axes required - one for each
line ('series').

Chart error
 

The macro recorder is pretty good, but it's only human.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ewan7279" wrote in message
...
The error was with the order in which the chart type was identified.
Solution:

Charts.Add

ActiveChart.SetSourceData Source:=Sheets("Gas").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Total Direct Cost"""
ActiveChart.SeriesCollection(2).Name = "=""Direct Cost Per Trade"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Gas Charts"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2
Axes" '<= after chart has data, not after 'Charts.Add'

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With

"ewan7279" wrote:

Hi all,

I ensounter a runtime error 1004 'Method 'Axes' of object '_Chart'
failed'
when I run this code (that I recorded):

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on
2
Axes"
ActiveChart.SetSourceData Source:=Sheets("Gas").Range(FirstMonth,
LastMonth.Offset(0, 2)), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Direct Cost Per Trade"""
ActiveChart.SeriesCollection(2).Name = "=""Total Direct Cost"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Gas Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Financials"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Direct
Cost"
.Axes(xlCategory, xlSecondary).HasTitle = False '<=== Causing the
error!!
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Direct
Cost
Per Trade"
End With

I'm a novice with VBA and have never used code and charts before so have
no
idea why this is happening.

The chart is a line graph with both Y Value axes required - one for each
line ('series').