Line-column chart: failing macros

[grautu]

Hi!
I encounter problems in obtaining a macro that inserts a "Line-column on 2 Axes" chart. The manual procedure always works and produces nice graphs but the code obtained by macro recorder always fails to work. After several attempts and variants I could say that implementing the secondary graph is the problem.
Here is the code (one of several variants which I tried):

Sub graph()
'
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.SetSourceData Source:=Sheets("MY_SHEET").Range("A1:C238"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="MY_SHEET"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "GRAPH"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VOLUME"
        .Axes(xlCategory, xlSecondary).HasTitle = True      '!!!HERE IS THE POINT!!!
        .Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "PRICE"
    End With
End Sub

REMARKS:
------------
Whenever trying to run the macro, one obtains the following message:

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

and the code line which throws that message is

.Axes(xlCategory, xlSecondary).HasTitle = True

Any other variant (including "False" choices instead of "True") yields identical effect at the same line, that is the first code line which concerns the secondary graph.
If asking for help (in the debug assistant form) then the systems opens an empty help form and then hangs until closing that empty form.
I've googled on this matter and discovered few similar examples but no one of them appeared to be solved meanwhile.
Could anybody please help me?
Grateful thanks in advance for any opinion!

 

[Andy Pope]

The problem is caused by the 2nd series not actually being on the secondary axis. This a result of the recorder code not being exact enough so that when repeated you get different results.

There are at least 3 ways to solve the problem.

1 - select the data range before the adding the chart
2 - re setting the chart type once the data has been set
3 - explicitly setting the series to the secondary axis

note only 1 of the options is needed.

Sub graph()
'
    ' 1 - select data range
    Sheets("MY_SHEET").Range("A1:C238").Select

    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
        
    ActiveChart.SetSourceData Source:=Sheets("MY_SHEET").Range("A1:C238"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="MY_SHEET"
    With ActiveChart

' 2 re set chart type
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
        .HasTitle = True
        .ChartTitle.Characters.Text = "GRAPH"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VOLUME"

' 3 set series to secondary axis        
'        .SeriesCollection(2).AxisGroup = 2
        
        .Axes(xlCategory, xlSecondary).HasTitle = True      '!!!HERE IS THE POINT!!!
        .Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "PRICE"
    End With
End Sub

 

[grautu]

SOLVED THANKS TO ANDY POPE

Great !!!
I've just tested your first solution for the prescribed range A1:C238 and it works very well. Now I may go on to edit the actual macro that should work with variable parameters (range, sheet name etc).
Thanks a lot !