Can you advise what this error is please Unable to get the find next property

[ipbr21054]

I am using this code & on occasion i get this message shown in red below but not sure where or how to fix it.

Please advise some steps i should be looking at Thanks

Private Sub ListBox1_Click()
    Dim CurRow As Long
    
    '~~> Current row selected
    CurRow = ListBox1.ListIndex
    
    '~~> Check if user selected anything
    If CurRow = -1 Then Exit Sub

    Dim ColA As Long, ColB As Long
    Dim SearchFirstValue As String, SearchSecondValue As String
    
    SearchFirstValue = ListBox1.List(CurRow, 0)
    SearchSecondValue = ListBox1.List(CurRow, 1)
        
    Select Case True
        Case Len(Trim(TextBoxName.Text)) <> 0
            ColA = 1
            ColB = 2
        Case Len(Trim(TextBoxReg.Text)) <> 0
            ColA = 2
            ColB = 1
        Case Len(Trim(TextBoxVehicle.Text)) <> 0
            ColA = 4
            ColB = 1
        Case Len(Trim(TextBoxKeyCode.Text)) <> 0
            ColA = 10
            ColB = 2
        Case Len(Trim(TextBoxChassisNumber.Text)) <> 0
            ColA = 12
            ColB = 2
    End Select
    
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim Rw As Long
    Dim NoRecordFound As Boolean: NoRecordFound = True
    
    '~~> Change this to the sheet where data is
    Set ws = ThisWorkbook.Sheets("Database")

    '~~> Search for car in column A because that is where the Customer name is
    Set aCell = ws.Columns(ColA).Find(What:=SearchFirstValue, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        Set bCell = aCell
        '~~> Check one column next to Col A because that is where the Car registration is
        If ws.Cells(aCell.Row, ColB).Value = SearchSecondValue Then
            NoRecordFound = False
            Rw = aCell.Row
        Else
            Do
                Set aCell = ws.Columns(1).FindNext(After:=aCell)
                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    If ws.Cells(aCell.Row, ColB).Value = SearchSecondValue Then
                        NoRecordFound = False
                        Rw = aCell.Row
                        Exit Do
                    End If
                Else
                    Exit Do
                End If
            Loop
        End If
    End If
    
    If NoRecordFound = True Then
        MsgBox "Not Found"
    Else
        ws.Range("A" & Rw).Select
        Unload Me
    End If
End Sub

 

[Alex Blakenburg]

Try changing your FindNext line to this.

Set aCell = ws.Columns(ColA).FindNext(After:=aCell)

I haven’t tested it but usually your Find and FindNext use the same range and I suspect it’s not happy about the “After:=“ reference not being in the search range.

PS: I have now tested it and that is definitely the case.