Change Chart to 2-Y Axis in VBA

[OldSwimmer1650]

The code is selecting a range of cells
Range(ActiveCell, ActiveCell.Offset(daysinmonth - 1, 1)).Select
right before it creates the chart tab. When the chart is created it plots only the second column of data(first column is correctly identified as X-axis). I'm trying to figure out how to plot the third column of date on the second Y-axis in the chart using VBA.

Option Explicit         ' Force explicit variable declaration.


Sub createweightchart()
 

    Dim chartsheet          As Chart        'new chart sheet to create
   
    Dim daysinmonth         As Integer      'number of days in the month
   
    Dim finddate            As Range        'cell of date to start
     
    Dim chartname           As String       'chart tab name
    Dim completedatetofind  As String       'this is the start date to find in table
    Dim monthchartname      As String       'name of new chart tab
    Dim monthcreate         As String       'this is the month to create chart
    Dim yearcreate          As String       'year for the corresponding month
   
   
    monthcreate = InputBox(Prompt:="What Month to Create?", _
                    Title:="To Create a Monthly Weight & BP Chart", _
                    Default:="Enter Month Here here")
 
    yearcreate = InputBox(Prompt:="What Year/Month to Create?", _
                    Title:="To Create a Monthly Weight & BP Chart", _
                    Default:="Enter Month Here here")
                   
    Worksheets("Weight Data").Select                                                'select tab where the weight data is
    completedatetofind = monthcreate & "/" & "1" & "/" & yearcreate                 'start date to find in table to create chart
    Set finddate = Range("D8:I6400").Find(what:=completedatetofind)                 'find the first of the month to create
    finddate.Select                                                                 'select that cell
    daysinmonth = Day(Application.WorksheetFunction.EoMonth(completedatetofind, 0)) 'how many days in this month
    Range(ActiveCell, ActiveCell.Offset(daysinmonth - 1, 1)).Select                 'select the range of data to plot date+2 columns of data
   
   
'Create Chart Tab
   
    If monthcreate = 1 Then
            monthchartname = "Jan"
        ElseIf monthcreate = 2 Then
            monthchartname = "Feb"
        ElseIf monthcreate = 3 Then
            monthchartname = "Mar"
        ElseIf monthcreate = 4 Then
            monthchartname = "Apr"
        ElseIf monthcreate = 5 Then
            monthchartname = "May"
        ElseIf monthcreate = 6 Then
            monthchartname = "Jun"
        ElseIf monthcreate = 7 Then
            monthchartname = "July"
        ElseIf monthcreate = 8 Then
            monthchartname = "Aug"
        ElseIf monthcreate = 9 Then
            monthchartname = "Sept"
        ElseIf monthcreate = 10 Then
            monthchartname = "Oct"
        ElseIf monthcreate = 11 Then
            monthchartname = "Nov"
        Else: monthchartname = "Dec"
    End If
   
    Charts.Add.Name = monthchartname & " " & yearcreate & " Weight Chart"
   
   

 
End Sub

 

[NdNoviceHlp]

You can trial the following code as long as you have selected all the data when making the chart. Adjust the sheet name and chart index number to suit. HTH. Dave

With Sheets("Sheet1").ChartObjects(1).Chart
.SeriesCollection(2).AxisGroup = 2
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
End With

To use vba to chart this without the need for selection...

Dim ChartRange As Range, X1Value As Range, Y1Value As Range, LastRow As Integer
With Sheets("Sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set X1Value = Sheets("Sheet1").Cells(1, 1)
Set Y1Value = Sheets("Sheet1").Cells(LastRow, 3)
Set ChartRange = Sheets("Sheet1").Range(X1Value, Y1Value)
Charts.Add.Location Where:=xlLocationAsObject, Name:="Sheet1"
Sheets("Sheet1").ChartObjects(1).Chart.ChartType = xlXYScatterSmoothNoMarkers
Sheets("Sheet1").ChartObjects(1).Placement = xlFreeFloating