Excel 2002
Can charts be created with multiple y axis scales and a common x-axis scale?
The VB Sub I have so far is this, but I would like to avoid Log Y axis and use a different scale for each series.
Sub Create_Chart1()
'
' Create_Chart1 Macro
' Create Chart of P3 - P7
'
'
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.PlotBy = xlColumns
' one series is present when chart is created
'ActiveChart.SeriesCollection.Add Source:=Worksheets("CryoData").Range("B9:B129")
ActiveChart.SeriesCollection.Add Source:=Worksheets("CryoData").Range("D9:D129")
ActiveChart.SeriesCollection.Add Source:=Worksheets("CryoData").Range("E9:E129")
ActiveChart.SeriesCollection.Add Source:=Worksheets("CryoData").Range("J9:J129")
' Set parameters and titles for series
With ActiveChart.SeriesCollection(1)
.Name = "P3"
.XValues = Worksheets("CryoData").Range("A9:A129")
.Values = Worksheets("CryoData").Range("B9:B129")
End With
With ActiveChart.SeriesCollection(2)
.Name = "P5"
.XValues = Worksheets("CryoData").Range("A9:A129")
'.Values = Worksheets("CryoData").Range("D9:D129")
End With
With ActiveChart.SeriesCollection(3)
.Name = "P7"
.XValues = Worksheets("CryoData").Range("A9:A129")
'.Values = Worksheets("CryoData").Range("E9:E129")
End With
With ActiveChart.SeriesCollection(4)
.Name = "TC"
.XValues = Worksheets("CryoData").Range("A9:A129")
'.Values = Worksheets("CryoData").Range("J9:J129")
.MarkerStyle = xlMarkerStyleCircle
End With
' Chart name, location
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart of P3,P5,P7,TC"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue) ' scales are automatic and logarithmic due to wide range of values.
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLogarithmic ' make sure that all items are scaled so they can be read.
.DisplayUnit = xlNone
End With
End Sub
Bookmarks