Macro for making a plot with primary and secondary axis in Excel RRS feed

  • Question

  • Please can anyone help with a macro for making an xyScatter plot with primary and secondary axis in Excel.
    Friday, December 23, 2011 3:59 PM


  • Sub AddScatterPlotWithTwoYAxes()
    Dim intPoints As Integer

    intPoints = Cells(Rows.Count, 2).End(xlUp).Row
    ' create the chart
    ActiveSheet.ChartObjects.Add Left:=50, Top:=50, Width:=500, Height:=500

    With ActiveSheet.ChartObjects(1).Chart
    .ChartType = xlXYScatter

    ' create series


    With .SeriesCollection(1)
        .Name = ActiveSheet.Range("B1").Value
        .XValues = ActiveSheet.Range("A2:A" & intPoints)
        .Values = ActiveSheet.Range("B2:B" & intPoints)
        .AxisGroup = xlPrimary
    End With


     With .SeriesCollection(2)
        .Name = ActiveSheet.Range("C1").Value
        .XValues = ActiveSheet.Range("A2:A" & intPoints)
        .Values = ActiveSheet.Range("C2:C" & intPoints)
        .AxisGroup = xlSecondary
    End With

    .HasAxis(xlValue, xlPrimary) = True
    .HasAxis(xlValue, xlSecondary) = True

    End With

    End Sub

    HTH, Bernie
    Friday, December 23, 2011 5:15 PM