VBA load array until value is found help

[smrmodel78]

I am trying to write a macro to load an array until a certain value is found. Right now, the user will enter the current month commission, i.e. March. I then need the array to only load the value in column C for February and January, (60 days) then stop so I can execute other code. The array will never have more than 5 elements. Is there a way to do this in a loop?

I am at a loss to figure out how to do that. I tried to hard code it, but then i still get an error message once it gets to January.

Sub CreateRetroComm()
    Dim wb1 As Workbook
    Dim wkshtname As String
    Dim colArray(1 To 5) As Variant
    Dim i As Range, rng As Range
    Dim lrow As Long, colcounter As Long, y As Long
    Dim StartHere As String, x As String, col As String
    Dim cell
    Dim sht As Worksheet
        
    Set wb1 = ThisWorkbook
    wkshtname = "Retro-" & wb1.Sheets("Instructions").Range("B4").Value
    StartHere = wb1.Sheets("Instructions").Range("B4")
    lrow = wb1.Sheets("Member Prem.Pymts").Cells(Rows.Count, 1).End(xlUp).Row
    
    'delete sheet if it exists
    For Each sht In wb1.Worksheets
        If sht.Name = wkshtname Then
            Application.DisplayAlerts = False
            wb1.Sheets(wkshtname).Delete
            Application.DisplayAlerts = True
        End If
    Next sht
    
    With wb1
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = wkshtname
  
        With .Sheets(wkshtname)
            .Cells(1).Resize(1, 8).Value = Array("ID", "Last Name", "First Name", "Premium", "Commission Amt", "month for", "agent", "sheet row")
        End With
    
        'returns Paid in Month 30-150 day columns
        col = Application.Match(StartHere, wb1.Sheets("Lookups").Range("$A$1:$A$13"), 0)
    
        colArray(1) = wb1.Sheets("Lookups").Cells(col - 1, 3)
        colArray(2) = wb1.Sheets("Lookups").Cells(col - 2, 3)
        colArray(3) = wb1.Sheets("Lookups").Cells(col - 3, 3)
        colArray(4) = wb1.Sheets("Lookups").Cells(col - 4, 3)
        colArray(5) = wb1.Sheets("Lookups").Cells(col - 5, 3)
        
        With .Sheets("Member Prem.Pymts") 'reference target sheet
            y = 1
            For colcounter = LBound(colArray, 1) To UBound(colArray, 1)
                x = 4 'starting row number data is found on
                For Each i In .Range(colArray(colcounter) & "4:" & colArray(colcounter) & lrow) 'loop through Member Prem.Payments column cells
                    If i.Value = StartHere Then
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = .Range("A" & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = .Range("B" & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = .Range("C" & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = .Range("BR" & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = wb1.Sheets("Commissions to Pay").Range(wb1.Sheets("Lookups").Cells(col - y, 4) & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = .Range(colArray(colcounter) & "2")
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "G").End(xlUp).Offset(1, 0) = .Range("DR" & x)
                        wb1.Sheets(wkshtname).Cells(Rows.Count, "H").End(xlUp).Offset(1, 0) = x
                    End If
                    
                    x = x + 1
                    
                    Next
    
            y = y + 1
            
            Next colcounter
        End With
            
        wb1.Sheets(wkshtname).Cells.EntireColumn.AutoFit

    End With
    
End Sub