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,
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