vba code to search through list

[panexel]

Hi,
I need to search through a list of part number matching a value. At the moment the code I have is not really efficient as it goes through the entire list of part numbers using a for loop. This takes a long time.

Is there a equivalent vba code to 'find' option, if so can I have some help with it.

After I find the matching part number in list 2 I need to delete it off or carry out another calculation. (following bit of code below shows a portion I have done to delete the entire line if a matching part number is found.)

SCount = Sheets("Specials").Range("A70000").End(xlUp).Row

For x = SCount To 2 Step -1
key = Sheets("Specials").Cells(x, "A").Value
For i = BCount To 2 Step -1
keyb = Sheets("Latest B").Cells(i, "C").Value
Application.StatusBar = "looking for Specials PN: " & key & " in Latest B PN: " & keyb
If key = keyb Then
Sheets("Latest B").Rows(i).EntireRow.Delete
End If
Next i
Next x

Thank you for all your help in advance.

 

[ColinKJ]

Hi panexel,

There is a "Find" function in VBA that you could use, but first, is there any chance the value you are searching for exists more than once in the list you are searching?.

Regards

ColinKJ

 

[panexel]

Hi ColinKJ
Thank you for your response.
There could be the possibility of it appearing more than once but highly unlikely. I will try to lookup on the syntax for the find function. It would be great if you could also help me on it.
Cheers!
panexel

 

[ColinKJ]

Hi panexel,

Try this code, it's not tested, but see how it goes:

SCount = Sheets("Specials").Range("A70000").End(xlUp).Row
For x = SCount To 2 Step -1
    key = Sheets("Specials").Cells(x, "A").Value
    c = 0
    On Error Resume Next
    c = Sheets("Latest B").Columns("C:C").Find(What:=key, LookIn:=xlValues).Row
    g = Err
    On Error GoTo 0
    If IsNumeric(c) = False Or g = 91 Or c = 0 Then GoTo nextx
    Sheets("Latest B").Rows(c).EntireRow.Delete
nextx:
Next x

Regards

Colin