Recorded Macro Codes, Contain Errors

[Guest]

Hi people

I had tried using Marco Recorder to get the codes required for plotting a chart
It appears that the codes are given incorrectly, there are certain part of the codes have runtime error

===============================================
Charts.Ad
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=
"Line - Column on 2 Axes

==================================================
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="asdasd
With ActiveChar
.HasTitle = Tru
.ChartTitle.Characters.Text = "charttitle
.Axes(xlCategory, xlPrimary).HasTitle = Fals
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "prod
.Axes(xlCategory, xlSecondary).HasTitle = Fals
.Axes(xlValue, xlSecondary).HasTitle = Tru
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "perce
End Wit
==========================================
The above codes are taken from 2 parts of the recorded marcos
it seems that everytime i reach

.Axes(xlCategory, xlSecondary).HasTitle = Fals
.Axes(xlValue, xlSecondary).HasTitle = Tru
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "perce

an error will occur, saying [Method 'Axes' of object '_chart' failed], if i comment those "xlSecondary" lines out, the codes will run find again

May i know is there a solution for this?

 

[Tushar Mehta]

Your error typically means that the particular axis doesn't exist or
doesn't support the necessary property. I don't see anyplace in the
code where you set the source range.

One problem that occurs when XL recorded code is executed is that the
SetSourceData statement follows the ChartType statement. Consequently,
under most conditions the ChartType statement faults or creates an
unexpected type of a chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

hi Tushar Mehta

I had seen your reply and realise the possibility of what you had mentioned
So pls let me post my fully recorded macr

Charts.Ad
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:=
"Line - Column on 2 Axes
ActiveChart.SetSourceData Source:=Sheets("Temporary").Range("H28"
ActiveChart.SeriesCollection.NewSerie
ActiveChart.SeriesCollection.NewSerie
ActiveChart.SeriesCollection.NewSerie
ActiveChart.SeriesCollection(1).XValues = "=Temporary!R2C1:R15C1
ActiveChart.SeriesCollection(1).Values = "=Temporary!R2C6:R15C6
ActiveChart.SeriesCollection(1).Name = "=Temporary!R1C6
ActiveChart.SeriesCollection(2).Values = "=Temporary!R2C8:R15C8
ActiveChart.SeriesCollection(2).Name = "=Temporary!R1C8
ActiveChart.SeriesCollection(3).Values = "=Temporary!R2C5:R15C5
ActiveChart.SeriesCollection(3).Name = "=Temporary!R1C5
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="asdasd
With ActiveChar
.HasTitle = Tru
.ChartTitle.Characters.Text = "charttitle
.Axes(xlCategory, xlPrimary).HasTitle = Fals
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "prod
.Axes(xlCategory, xlSecondary).HasTitle = Fals
.Axes(xlValue, xlSecondary).HasTitle = Tru
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "perce
End Wit
With ActiveChart.Axes(xlCategory
.HasMajorGridlines = Fals
.HasMinorGridlines = Fals
End Wit
With ActiveChart.Axes(xlValue
.HasMajorGridlines = Tru
.HasMinorGridlines = Fals
End Wit
ActiveChart.HasLegend = Fals
ActiveChart.HasDataTable = Tru
ActiveChart.DataTable.ShowLegendKey = Tru
ActiveChart.ChartArea.Selec
ActiveChart.PlotArea.Selec
With Selection.Borde
.ColorIndex = 1
.Weight = xlThi
.LineStyle = xlContinuou
End Wit
With Selection.Interio
.ColorIndex =
.PatternColorIndex =
.Pattern = xlSoli
End Wit
ActiveChart.SeriesCollection(3).Selec
With Selection.Borde
.ColorIndex =
.Weight = xlThic
.LineStyle = xlContinuou
End Wit
With Selectio
.MarkerBackgroundColorIndex =
.MarkerForegroundColorIndex = xlAutomati
.MarkerStyle = xlSta
.Smooth = Fals
.MarkerSize =
.Shadow = Fals
End Wit
ActiveChart.PlotArea.Selec
ActiveChart.SeriesCollection(2).Selec
With Selection.Borde
.Weight = xlThi
.LineStyle = xlAutomati
End Wit
Selection.Shadow = Fals
Selection.InvertIfNegative = Fals
With Selection.Interio
.ColorIndex =
.Pattern = xlSoli
End Wit
ActiveChart.PlotArea.Selec
ActiveChart.SeriesCollection(1).Selec
With Selection.Borde
.Weight = xlThi
.LineStyle = xlAutomati
End Wit
Selection.Shadow = Fals
Selection.InvertIfNegative = Fals
With Selection.Interio
.ColorIndex =
.Pattern = xlSoli
End Wit
ActiveChart.PlotArea.Selec
End Su
===============================================

Actually I purposely did not select SourceRange ,as you can see ("H28"), because i wanted to do it manully through adding of series to get to the chart that i am expecting

Using SourceRange will only result in developing an unexpected chart, like you had mentioned

Although I am not sure, from what i see, I guess that the compiler do not recognise the TypeName:= "Line - Column on 2 Axes

So may I know is there any solution?

 

[Jon Peltier]

Following Tushar's lead, I'll suggest that you rearrange the steps. Put
this line:

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

after the last one which defines your series:

ActiveChart.SeriesCollection(3).Name = "=Temporary!R1C5"

Right after your existing code assigns the chart type, you change the
source data to a single cell. This can only produce one set of axes, so
Excel drops the secondary axes, then forgets it ever had them. If your
chart has sufficient data, then Excel apportions the series on the two axes.

A more robust approach is to forget the xlBuiltIn type, just make the
chart a column chart, then when you assign .Values and .XValues, also
assign .AxisGroup and .ChartType to each series.

- Jon

 

[Guest]

Your solution works, Thank You.