I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A").EntireColumn.AutoFit

Set sel = Range("A12")
Set sel2 = Range("A33")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Your code is looks pretty much as I get from the macro recorder. Yet I get
intermittent failures with your code, in particular applying the 2-axis
custom chart type..

Swapping the order of these lines seems to make it more reliable, at least
in my light testing

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"), _
PlotBy:=xlRows
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

You don't need to rely on custom charts from the user gallery, set each
series to whichever axis and whatever chart type, eg

.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers

but don't try changing any properties of the secondary axis until at least
one series is on that axis.

You might also look into adding a chartobject to the sheet to size and
position, eg

Sub test()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
With cht
..SetSourceData Range("A13"), xlRows
..SeriesCollection(2).AxisGroup = 2
..SeriesCollection(2).ChartType = xlLineMarkers
' more stuff with cht & axes
End With

End Sub

Regards
Peter T

"Gleam" <(E-Mail Removed)> wrote in message
news:3B19459F-5E11-4954-9C93-(E-Mail Removed)...
> I would like a macro to create a chart with twin columns but different
axes.
> I have recorded a macro using "Line - Column on 2 Axes" which is the
closest
> towhat Iwant but when I run it, the code fails. (Excel 2003)
> Here is the code with a comment at the point where it falls over:
>
> Sub ChartTest()
> Dim sel As Range, sel2 As Range
>
> [a1] = "Causes"
> [b1] = "Roll Changes"
> [c1] = "Strip Breaks"
> [d1] = "Cobbles"
>
> [a2] = "Time Lost"
> [b2] = 220
> [c2] = 64
> [d2] = 5
>
> [a3] = "Occurences"
> [b3] = 12
> [c3] = 4
> [d3] = 1
>
> Columns("A").EntireColumn.AutoFit
>
> Set sel = Range("A12")
> Set sel2 = Range("A33")
>
> ' had to add Activeworkbook to next line
> ActiveWorkbook.Charts.Add
> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
> "Line - Column on 2 Axes"
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"),
> PlotBy:= _
> xlRows
> ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = "Delay Causes"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
> (min)"
> ' fails on next line
> .Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
> .Axes(xlValue, xlSecondary).HasTitle = True
> .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"Occurences"
> End With
> ActiveChart.HasLegend = False
> ActiveChart.HasDataTable = True
> ActiveChart.DataTable.ShowLegendKey = True
> ActiveChart.Axes(xlValue, xlSecondary).Select
> With ActiveChart.Axes(xlValue, xlSecondary)
> .MinimumScaleIsAuto = True
> .MaximumScale = .MaximumScale * 2
> .MinorUnitIsAuto = True
> .MajorUnitIsAuto = True
> .Crosses = xlAutomatic
> .ReversePlotOrder = False
> .ScaleType = xlLinear
> .DisplayUnit = xlNone
> End With
> End Sub
>

Thank you for this. It is now running. I had to comment out the line

.SeriesCollection(2).AxisGroup = 2

When I set the line type to xlColumnClustered for series 2, the columns for
series 2 appear on top of the columns for series 1.
Is there a way to get them side by side?

"Peter T" wrote:

> Your code is looks pretty much as I get from the macro recorder. Yet I get
> intermittent failures with your code, in particular applying the 2-axis
> custom chart type..
>
> Swapping the order of these lines seems to make it more reliable, at least
> in my light testing
>
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"), _
> PlotBy:=xlRows
> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
> "Line - Column on 2 Axes"
>
> You don't need to rely on custom charts from the user gallery, set each
> series to whichever axis and whatever chart type, eg
>
> .SeriesCollection(2).AxisGroup = 2
> .SeriesCollection(2).ChartType = xlLineMarkers
>
> but don't try changing any properties of the secondary axis until at least
> one series is on that axis.
>
> You might also look into adding a chartobject to the sheet to size and
> position, eg
>
> Sub test()
> Dim cht As Chart
>
> Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
> With cht
> ..SetSourceData Range("A13"), xlRows
> ..SeriesCollection(2).AxisGroup = 2
> ..SeriesCollection(2).ChartType = xlLineMarkers
> ' more stuff with cht & axes
> End With
>
> End Sub
>
> Regards
> Peter T
>
> "Gleam" <(E-Mail Removed)> wrote in message
> news:3B19459F-5E11-4954-9C93-(E-Mail Removed)...
> > I would like a macro to create a chart with twin columns but different
> axes.
> > I have recorded a macro using "Line - Column on 2 Axes" which is the
> closest
> > towhat Iwant but when I run it, the code fails. (Excel 2003)
> > Here is the code with a comment at the point where it falls over:
> >
> > Sub ChartTest()
> > Dim sel As Range, sel2 As Range
> >
> > [a1] = "Causes"
> > [b1] = "Roll Changes"
> > [c1] = "Strip Breaks"
> > [d1] = "Cobbles"
> >
> > [a2] = "Time Lost"
> > [b2] = 220
> > [c2] = 64
> > [d2] = 5
> >
> > [a3] = "Occurences"
> > [b3] = 12
> > [c3] = 4
> > [d3] = 1
> >
> > Columns("A").EntireColumn.AutoFit
> >
> > Set sel = Range("A12")
> > Set sel2 = Range("A33")
> >
> > ' had to add Activeworkbook to next line
> > ActiveWorkbook.Charts.Add
> > ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
> > "Line - Column on 2 Axes"
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"),
> > PlotBy:= _
> > xlRows
> > ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
> > With ActiveChart
> > .HasTitle = True
> > .ChartTitle.Characters.Text = "Delay Causes"
> > .Axes(xlCategory, xlPrimary).HasTitle = False
> > .Axes(xlValue, xlPrimary).HasTitle = True
> > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
> > (min)"
> > ' fails on next line
> > .Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
> > .Axes(xlValue, xlSecondary).HasTitle = True
> > .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> "Occurences"
> > End With
> > ActiveChart.HasLegend = False
> > ActiveChart.HasDataTable = True
> > ActiveChart.DataTable.ShowLegendKey = True
> > ActiveChart.Axes(xlValue, xlSecondary).Select
> > With ActiveChart.Axes(xlValue, xlSecondary)
> > .MinimumScaleIsAuto = True
> > .MaximumScale = .MaximumScale * 2
> > .MinorUnitIsAuto = True
> > .MajorUnitIsAuto = True
> > .Crosses = xlAutomatic
> > .ReversePlotOrder = False
> > .ScaleType = xlLinear
> > .DisplayUnit = xlNone
> > End With
> > End Sub
> >
>
>
>

Here's how to do it manually:

http://peltiertech.com/Excel/Charts/...OnTwoAxes.html

Follow the protocol with the macro recorder on while doing this manually,
and merge the recorded code with your existing procedure.

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


"Gleam" <(E-Mail Removed)> wrote in message
news:B81459E4-524E-4C1D-BE1B-(E-Mail Removed)...
> Thank you for this. It is now running. I had to comment out the line
>
> .SeriesCollection(2).AxisGroup = 2
>
> When I set the line type to xlColumnClustered for series 2, the columns
> for
> series 2 appear on top of the columns for series 1.
> Is there a way to get them side by side?
>
> "Peter T" wrote:
>
>> Your code is looks pretty much as I get from the macro recorder. Yet I
>> get
>> intermittent failures with your code, in particular applying the 2-axis
>> custom chart type..
>>
>> Swapping the order of these lines seems to make it more reliable, at
>> least
>> in my light testing
>>
>> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"), _
>> PlotBy:=xlRows
>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>> "Line - Column on 2 Axes"
>>
>> You don't need to rely on custom charts from the user gallery, set each
>> series to whichever axis and whatever chart type, eg
>>
>> .SeriesCollection(2).AxisGroup = 2
>> .SeriesCollection(2).ChartType = xlLineMarkers
>>
>> but don't try changing any properties of the secondary axis until at
>> least
>> one series is on that axis.
>>
>> You might also look into adding a chartobject to the sheet to size and
>> position, eg
>>
>> Sub test()
>> Dim cht As Chart
>>
>> Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
>> With cht
>> ..SetSourceData Range("A13"), xlRows
>> ..SeriesCollection(2).AxisGroup = 2
>> ..SeriesCollection(2).ChartType = xlLineMarkers
>> ' more stuff with cht & axes
>> End With
>>
>> End Sub
>>
>> Regards
>> Peter T
>>
>> "Gleam" <(E-Mail Removed)> wrote in message
>> news:3B19459F-5E11-4954-9C93-(E-Mail Removed)...
>> > I would like a macro to create a chart with twin columns but different
>> axes.
>> > I have recorded a macro using "Line - Column on 2 Axes" which is the
>> closest
>> > towhat Iwant but when I run it, the code fails. (Excel 2003)
>> > Here is the code with a comment at the point where it falls over:
>> >
>> > Sub ChartTest()
>> > Dim sel As Range, sel2 As Range
>> >
>> > [a1] = "Causes"
>> > [b1] = "Roll Changes"
>> > [c1] = "Strip Breaks"
>> > [d1] = "Cobbles"
>> >
>> > [a2] = "Time Lost"
>> > [b2] = 220
>> > [c2] = 64
>> > [d2] = 5
>> >
>> > [a3] = "Occurences"
>> > [b3] = 12
>> > [c3] = 4
>> > [d3] = 1
>> >
>> > Columns("A").EntireColumn.AutoFit
>> >
>> > Set sel = Range("A12")
>> > Set sel2 = Range("A33")
>> >
>> > ' had to add Activeworkbook to next line
>> > ActiveWorkbook.Charts.Add
>> > ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
>> > "Line - Column on 2 Axes"
>> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"),
>> > PlotBy:= _
>> > xlRows
>> > ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
>> > With ActiveChart
>> > .HasTitle = True
>> > .ChartTitle.Characters.Text = "Delay Causes"
>> > .Axes(xlCategory, xlPrimary).HasTitle = False
>> > .Axes(xlValue, xlPrimary).HasTitle = True
>> > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time
>> > Lost
>> > (min)"
>> > ' fails on next line
>> > .Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
>> > .Axes(xlValue, xlSecondary).HasTitle = True
>> > .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
>> "Occurences"
>> > End With
>> > ActiveChart.HasLegend = False
>> > ActiveChart.HasDataTable = True
>> > ActiveChart.DataTable.ShowLegendKey = True
>> > ActiveChart.Axes(xlValue, xlSecondary).Select
>> > With ActiveChart.Axes(xlValue, xlSecondary)
>> > .MinimumScaleIsAuto = True
>> > .MaximumScale = .MaximumScale * 2
>> > .MinorUnitIsAuto = True
>> > .MajorUnitIsAuto = True
>> > .Crosses = xlAutomatic
>> > .ReversePlotOrder = False
>> > .ScaleType = xlLinear
>> > .DisplayUnit = xlNone
>> > End With
>> > End Sub
>> >
>>
>>
>>

Not sure why you need to comment out
> .SeriesCollection(2).AxisGroup = 2

A vba demo just for fun, alternating columns and stacked-columns on the
second axis. Not the best way of arranging the data, just for illustration.

Sub MakeSource()
Dim vArr
Range("B2:E5") = "dummy" 'normally blank or zero
Range("B1,D1") = "Ripe": Range("C1,E1") = "Rotten"

vArr = Array("Apples", "%", "Pears", "%")
Range("A2:A5") = Application.Transpose(vArr)

Range("B2:C2,B4:C4").Formula = "=INT(RAND()*100)"
Range("D3:E3,D5:E5").FormulaR1C1 = "=R[-1]C[-2]"

End Sub

Sub DualChart()
Dim i As Long
Dim cht As Chart
Dim sr As Series

MakeSource

With Range("b7")
Set cht = ActiveSheet.ChartObjects.Add(.Left, .Top, 300, 200).Chart
End With

cht.SetSourceData Range("A1:E5"), xlColumns

For i = 3 To 4
With cht.SeriesCollection(i)
.AxisGroup = xlSecondary
.ChartType = xlColumnStacked100
.Interior.Color = cht.SeriesCollection(i - 2).Interior.Color
End With
Next

cht.HasLegend = True
For i = 4 To 3 Step -1
cht.Legend.LegendEntries(i).Delete
Next
End Sub

Press F9

Regards,
Peter T

"Gleam" <(E-Mail Removed)> wrote in message
news:B81459E4-524E-4C1D-BE1B-(E-Mail Removed)...
> Thank you for this. It is now running. I had to comment out the line
>
> .SeriesCollection(2).AxisGroup = 2
>
> When I set the line type to xlColumnClustered for series 2, the columns
for
> series 2 appear on top of the columns for series 1.
> Is there a way to get them side by side?
>
> "Peter T" wrote:
>
> > Your code is looks pretty much as I get from the macro recorder. Yet I
get
> > intermittent failures with your code, in particular applying the 2-axis
> > custom chart type..
> >
> > Swapping the order of these lines seems to make it more reliable, at
least
> > in my light testing
> >
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"), _
> > PlotBy:=xlRows
> > ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
> > "Line - Column on 2 Axes"
> >
> > You don't need to rely on custom charts from the user gallery, set each
> > series to whichever axis and whatever chart type, eg
> >
> > .SeriesCollection(2).AxisGroup = 2
> > .SeriesCollection(2).ChartType = xlLineMarkers
> >
> > but don't try changing any properties of the secondary axis until at
least
> > one series is on that axis.
> >
> > You might also look into adding a chartobject to the sheet to size and
> > position, eg
> >
> > Sub test()
> > Dim cht As Chart
> >
> > Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
> > With cht
> > ..SetSourceData Range("A13"), xlRows
> > ..SeriesCollection(2).AxisGroup = 2
> > ..SeriesCollection(2).ChartType = xlLineMarkers
> > ' more stuff with cht & axes
> > End With
> >
> > End Sub
> >
> > Regards
> > Peter T
> >
> > "Gleam" <(E-Mail Removed)> wrote in message
> > news:3B19459F-5E11-4954-9C93-(E-Mail Removed)...
> > > I would like a macro to create a chart with twin columns but different
> > axes.
> > > I have recorded a macro using "Line - Column on 2 Axes" which is the
> > closest
> > > towhat Iwant but when I run it, the code fails. (Excel 2003)
> > > Here is the code with a comment at the point where it falls over:
> > >
> > > Sub ChartTest()
> > > Dim sel As Range, sel2 As Range
> > >
> > > [a1] = "Causes"
> > > [b1] = "Roll Changes"
> > > [c1] = "Strip Breaks"
> > > [d1] = "Cobbles"
> > >
> > > [a2] = "Time Lost"
> > > [b2] = 220
> > > [c2] = 64
> > > [d2] = 5
> > >
> > > [a3] = "Occurences"
> > > [b3] = 12
> > > [c3] = 4
> > > [d3] = 1
> > >
> > > Columns("A").EntireColumn.AutoFit
> > >
> > > Set sel = Range("A12")
> > > Set sel2 = Range("A33")
> > >
> > > ' had to add Activeworkbook to next line
> > > ActiveWorkbook.Charts.Add
> > > ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
> > > "Line - Column on 2 Axes"
> > > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A13"),
> > > PlotBy:= _
> > > xlRows
> > > ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
> > > With ActiveChart
> > > .HasTitle = True
> > > .ChartTitle.Characters.Text = "Delay Causes"
> > > .Axes(xlCategory, xlPrimary).HasTitle = False
> > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time
Lost
> > > (min)"
> > > ' fails on next line
> > > .Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
> > > .Axes(xlValue, xlSecondary).HasTitle = True
> > > .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
> > "Occurences"
> > > End With
> > > ActiveChart.HasLegend = False
> > > ActiveChart.HasDataTable = True
> > > ActiveChart.DataTable.ShowLegendKey = True
> > > ActiveChart.Axes(xlValue, xlSecondary).Select
> > > With ActiveChart.Axes(xlValue, xlSecondary)
> > > .MinimumScaleIsAuto = True
> > > .MaximumScale = .MaximumScale * 2
> > > .MinorUnitIsAuto = True
> > > .MajorUnitIsAuto = True
> > > .Crosses = xlAutomatic
> > > .ReversePlotOrder = False
> > > .ScaleType = xlLinear
> > > .DisplayUnit = xlNone
> > > End With
> > > End Sub
> > >
> >
> >
> >