Search through a column

[jcooper]

Hey All - newbie to the board and to macro programming, but not to programming in general. I'm currently trying to improve a macro that someone else wrote. It takes two lists of people (one old, one new), finds the matching entries by comparing ID numbers, then records any changes that have been made to that person's data. The macro currently takes about 10 minutes to run, and I think this could be remedied by improving the search function. Currently it's this:

Range("A1").Select 'Column A contains the ID numbers

Do While ActiveCell.Value <> id_number
search_i = search_i + 1
search_cell = "A" & search_i
Range(search_cell).Select
If ActiveCell.Value = "" Then
search_i = search_i_temp
Exit Do
End If
Loop

All I need to do is find out which row the id_number is in, or if the value doesn't exist in that column. Should be an exact match with the contents of the cell, not a substring.

What's the fastest way to find the information I need? Is there some way I can take advantage of the fact that the lists are sorted by ID number in order to speed things up?

Thanks in advance for any help you can give me.

 

[VoG]

Hello and welcome to MrExcel.

It is not clear what that code is looking for. Anyway, I would suggest using Find

Sub srch()
Dim Found1 As Range
Set Found1 = Columns("A").Find(What:=Range("B2").Value, LookAt:=xlWhole, LookIn:=xlValues)
If Not Found1 Is Nothing Then MsgBox Range("B2").Value & " found in " & Found1.Address(False, False), vbInformation
End Sub

which searches for the value of B2 in column A and reports the matching row.

 

[rdwray]

VoG has the right idea, here is my version:

Sub Macro1()
    ID = "B4" ' Change B4 to the ID number. Note the quotation marks around the ID.
    ' The ID can be a cell like: ID = Range("B1")
    Range("A:A").Select
    On Error GoTo NotFound
    Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
NotFound:
End Sub

There isn't any fast way then this.