Macro unique number lookup - exact match required

[davidf120]

Hi All,

I am using the following code:

Sub ReturnInvoice()
Do


Dim myWord$
myWord = InputBox("Insert the Voucher Number", "Voucher Search")
If myWord = "" Then Exit Sub


Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Sheets("Returned").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
For xRow = 2 To LastRow
If InStr(Cells(xRow, 1).Value, myWord) > 0 Then
Rows(xRow).Cut Sheets("Returned").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
Sheets("Returned").Range("J" & Rows.Count).End(xlUp).Offset(1).Value = Now
Sheets("Invoices Out").Select
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Loop


End Sub

Which I sourced I think on here for someone else's issue. However I have encountered a bit of an issue with it.

Basically the macro pops up a lookup box, in which we put in a unique voucher number, it finds that number and then copies the entire row into another sheet.

Issue is if for example I search for voucher number "5", the macro is finding every cell with a 5 in it and returning all those rows, eg 15, 25 55 etc. I only want it to return the exact number entered.

Any ideas? I tried playing with lookat whole but didn't seem to work.

 

[wideboydixon]

This line ...

If InStr(Cells(xRow, 1).Value, myWord) > 0 Then

... is looking for any cells that contain the search criteria. You could try changing it to ...

If Cells(xRow, 1).Value = myWord Then

WBD

 

[davidf120]

This line ...

If InStr(Cells(xRow, 1).Value, myWord) > 0 Then

... is looking for any cells that contain the search criteria. You could try changing it to ...

If Cells(xRow, 1).Value = myWord Then

WBD

That's superb! Thank you!