VBA IF condition with an error that just works for the first loop-CodePudding

I am trying to create a 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.

I am trying to create a VBA IF condition that search for a specific name, which the names 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.

If Worksheets("data").Cells(2, i) = Worksheets("main").Cells(3, 3) Then

The thing is that this condition just works for the first name and I dont know why. Also there is an error that says:

Run-time error "13": Type mismatch

The full vba code that I have:´

Sub graph()

Dim chrt As ChartObject
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long

'Find the last used row
LastRow = Sheets("vol").Range("B65536").End(xlUp).Row

'Find the last used column
LastColumn = 500

'Looping from second row till last row which has the data
For i = 2 To LastColumn

If Worksheets("vol").Cells(2, i) = Worksheets("main").Cells(3, 3) Then

  'Sheet main is selected bcoz charts will be inserted here
    Sheets("main").Select

    ActiveSheet.Shapes.AddChart.Select
    'this adds the chart and selects it in the same statement
    ActiveChart.ChartType = xlLine

    'now the line chart is added...setting its data source here
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "25%"
    ActiveChart.SeriesCollection(1).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
    ActiveChart.SeriesCollection(1).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i   1), Worksheets("data").Cells(LastRow, i   1))

    'now the line chart is added...setting its data source here
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "50%"
    ActiveChart.SeriesCollection(2).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
    ActiveChart.SeriesCollection(2).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i   6), Worksheets("data").Cells(LastRow, i   6))

    'now the line chart is added...setting its data source here
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "25%"
    ActiveChart.SeriesCollection(3).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
    ActiveChart.SeriesCollection(3).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i   11), Worksheets("data").Cells(LastRow, i   11))

    With Worksheets("main").ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Text = "1 month"
    End With
    
End If
     
i = i   15
    
Next i


End Sub

Could someone illuminate me, please!!!

Thank you so much

CodePudding user response：

Some suggested changes, including the Step 15

Sub graph()

    Dim cht As Chart
    Dim i As Long
    Dim LastRow As Long, rngX As Range, rngY As Range
    Dim LastColumn As Long, wsMain As Worksheet, wsVol As Worksheet, wsData As Worksheet

    Set wsVol = ThisWorkbook.Worksheets("vol")
    Set wsData = ThisWorkbook.Worksheets("data")
    Set wsMain = ThisWorkbook.Worksheets("Main")
    
    For i = 2 To 500 Step 15 'loop in increments of 15
    
        If wsVol.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 cht = wsMain.Shapes.AddChart.Chart
            cht.ChartType = xlLine
            'remove any "auto-added" series (if data was selected when chart was added)
            Do While cht.SeriesCollection.Count > 0
                cht.SeriesCollection(1).Delete
            Loop
            
            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 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