excelvbalistboxfindmultiple-matches

Best way to find matches in sheet compared to listbox vba


I basically want to know what is the best way to compare items in a sheet to a listbox, basically, items in listbox need to be found on a sheet, if they are not found they need to go to the bottom of the sheet on the next free row,

This shouldn't be too hard but I've tried using and if statement but finds it has to search all of the rows on the sheet with all of the rows within the listbox which makes it laggy, time-consuming and unresponsive at times,

I was thinking of using the .find method but didn't want to waste my time,

Update of code:

`

          For i = 0 To Me.ListBox1.ListCount - 1

            field1 = Me.ListBox1.List(i)
            field2 = Me.ListBox1.List(i, 1)
            field3 = Me.ListBox1.List(i, 2)


            field2ammend = Right(field2, Len(field2) - 7)


    For Each rCell In rRng.Cells






        If rCell.Value = field1 Then

            comp = field3
            name = field2ammend

            Sheets("Hair").Range("E" & rCell.Row) = comp
            Sheets("Hair").Range("F" & rCell.Row) = name

            Range("A" & rCell.Row & ":H" & rCell.Row).Interior.ColorIndex = 24

            countgood = countgood + 1




        Else




            ListBox2.AddItem (field2)

            'bal = bal + 1
            'Sheets("Hair").Range("B" & lastrows) = field1
            'Sheets("Hair").Range("E" & lastrows) = comp
            'Sheets("Hair").Range("F" & lastrows) = name
            'Range("A" & lastrows & ":H" & lastrows).Interior.ColorIndex = 24
            'lastrows = lastrows + 1
            'countbad = countbad + 1
        End If

Next rCell Next i

`

Any suggestions,

Thank you,


Solution

  • I don't know the Reason, but, does this help?

    Sub Weiter_Click()
    Dim i As Integer
    Dim varTemp As Variant
    
        varTemp = ListBox1.List '<-- for listboxes with one column. you have to edit this to pass your code
    
        For i = LBound(varTemp) To UBound(varTemp)
            If Columns("A:A").Find(varTemp(i, 0)) Is Nothing Then '<--- there is the column you're looking in
                Cells(1, 1).End(xlDown).Offset(1, 0).Value = varTemp(i, 0)
            Else
                Columns("A:A").Find(varTemp(i, 0)).Interior.ColorIndex = 3 '<--- 3 is red, idk what color you want
            End If
        Next
    End Sub