Replicate a VBA code (that works for a 1st graph) to a 2nd graph in the same SUB-CodePudding

I have a VBA code that is working just for the first graph.

I am trying to create a second graph using VBA. First of all, I have a dashboard and I would like to have a main sheet that summarize some name that I want to search for, so all my data are in other sheets. Also I would like to create more than one graph in the main sheet.

I created a VBA IF condition that search for a specific name, which the names that are in the data sheet are like this:

data sheet

With that in mind, I dim an "i" to find the name that I want in the data sheet. When someone wants to search for name1 or name 2..., the person will choose the name in main sheet, which the cell is: "C3". If C3 cell in main sheet is equal to the data sheet B2 or Q2... then create a graph.

So the code for just one graph (that works) is:

Private Sub worksheet_change(ByVal target As Range)
    Dim cht As Chart, co As Object
    Dim i As Long
    Dim LastRow As Long, rngX As Range, rngY As Range
    Dim LastColumn As Long, wsMain As Worksheet, wsData As Worksheet
    Set wsData = ThisWorkbook.Worksheets("data")
    Set wsMain = ThisWorkbook.Worksheets("Main")
  If target = wsMain.Cells(3, 3) Then
    For i = 2 To 500 Step 15 'loop in increments of 15
        If wsData.Cells(2, i) = wsMain.Cells(3, 3) Then
            'define data ranges
            Set rngX = wsData.Range(wsData.Cells(6, i), wsData.Cells(Rows.Count, i).End(xlUp))
            Set rngY = rngX.Offset(0, 1)
            ClearWorksheetCharts wsMain 'remove any existing chart(s)
            With wsMain.Range("B22:H37")
                'add chartobject, setting position and size
                Set co = .Worksheet.Shapes.AddChart(xlLine, .Left, .Top, _
                                                     .Width, .Height)
            End With
            Set cht = co.Chart
            ClearChartSeries cht 'remove any "auto-added" series (if data was selected when chart was added)
            AddSeries cht, "25%", rngX, rngY
            AddSeries cht, "50%", rngX, rngY.Offset(0, 5)
            AddSeries cht, "25%", rngX, rngY.Offset(0, 10)
            cht.HasTitle = True
            cht.ChartTitle.Text = "1 month"
         End If
    Next i
  End if
End Sub
'add a series and name it (factored out from main sub)
Sub AddSeries(cht As Chart, serName As String, serX, serY)
    With cht.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
End Sub
'remove any existing series from a chart
Sub ClearChartSeries(cht As Chart)
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
    Loop
End Sub
'Remove any chart objects from `ws`
Sub ClearWorksheetCharts(ws As Worksheet)
    Do While ws.ChartObjects.Count > 0
        ws.ChartObjects(1).Delete
    Loop
End Sub

To replicate the same code for the second graph using the same sub I did:

Private Sub worksheet_change(ByVal target As Range)

    Dim cht As Chart, cht2 As Chart, co As Object, co2 As Object
    Dim i As Long
    Dim LastRow As Long, rngX As Range, rngY As Range, rngX2 As Range, rngY2 As Range
    Dim LastColumn As Long, wsMain As Worksheet, wsData As Worksheet

    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsMain = ThisWorkbook.Worksheets("Main")
    
If target = wsMain.Cells(3, 3) Then
    
    For i = 2 To 500 Step 15 'loop in increments of 15
    
        If wsData.Cells(2, i) = wsMain.Cells(3, 3) Then
            'define data ranges
            Set rngX = wsData.Range(wsData.Cells(6, i), wsData.Cells(Rows.Count, i).End(xlUp))
            Set rngY = rngX.Offset(0, 1)
            Set rngX2 = rngX
            Set rngY2 = rngX2.Offset(0, 2)
            
            ClearWorksheetCharts wsMain 'remove any existing chart(s)
            With wsMain.Range("B22:H37")
                'add chartobject, setting position and size
                Set co = .Worksheet.Shapes.AddChart(xlLine, .Left, .Top, _
                                                     .Width, .Height)
            
            End With
            
            With wsMain.Range("B39:H54")
                'add chartobject, setting position and size
                Set co2 = .Worksheet.Shapes.AddChart(xlLine, .Left, .Top, _
                                                     .Width, .Height)
                                                                                           
            End With
            
            Set cht = co.Chart
            ClearChartSeries cht 'remove any "auto-added" series (if data was selected when chart was added)
            
            AddSeries cht, "25%", rngX, rngY
            AddSeries cht, "50%", rngX, rngY.Offset(0, 5)
            AddSeries cht, "25%", rngX, rngY.Offset(0, 10)
            
            cht.HasTitle = True
            cht.ChartTitle.Text = "1 month"
            
            Set cht2 = co2.Chart
            ClearChartSeries cht2 'remove any "auto-added" series (if data was selected when chart was added)
            
            AddSeries cht2, "25%", rngX2, rngY2
            AddSeries cht2, "50%", rngX2, rngY2.Offset(0, 6)
            AddSeries cht2, "25%", rngX2, rngY2.Offset(0, 11)
            
            cht2.HasTitle = True
            cht2.ChartTitle.Text = "2 months"
         End If
    Next i
End If
End Sub

'add a series and name it (factored out from main sub)
Sub AddSeries(cht As Chart, cht2 As Chart, serName As String, serX, serY)
    With cht.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
    
    With cht2.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
End Sub

'remove any existing series from a chart
Sub ClearChartSeries(cht As Chart, cht2 As Chart)
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
        cht2.SeriesCollection(2).Delete
    Loop
End Sub

'Remove any chart objects from `ws`
Sub ClearWorksheetCharts(ws As Worksheet)
    Do While ws.ChartObjects.Count > 0
        ws.ChartObjects(1).Delete
    Loop
End Sub

The thing is that I receive an error in the first clear chart:

ClearChartSeries cht

And the error is:

Compile error: Argument not optional

What am I doing wrong?

CodePudding user response：

What am I doing wrong?

Revert

Sub AddSeries(cht As Chart, cht2 As Chart, serName As String, serX, serY)
    With cht.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
    
    With cht2.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
End Sub

'remove any existing series from a chart
Sub ClearChartSeries(cht As Chart, cht2 As Chart)
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
        cht2.SeriesCollection(2).Delete
    Loop
End Sub

Sub AddSeries(cht As Chart, serName As String, serX, serY)
    With cht.SeriesCollection.NewSeries
        .Name = serName
        .XValues = serX
        .Values = serY
    End With
End Sub

'remove any existing series from a chart
Sub ClearChartSeries(cht As Chart)
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
    Loop
End Sub