Cells.Find Part 3

[oldjay]

The following Sub works fine except if the first col
doesn't have a match it continues searching the other
cols.
I want it to quit and say "No match found"





Sub FindCustomer() 'This selects customer from customer
list based on what is in Cel B1

Sheets("Customer").Select
Range("B1").Select
Cells.Find(What:=ActiveCell & "*", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:=False) _
.Activate

End Sub
I want to thank all the persons who replied in the pas

 

[Don Guillett]

One thing to do is change cells. to columns(1) for col A

 

[NickHK]

oldjay,
As you're using "Cells", that refers to all cells on the worksheet. Restrict
your search to say Range("B2:B200").Find....

If no match is found, your active cell remains at B1, so test for the active
cell address and show a msgbox if neccessary.


NickHk

 

[Tom Ogilvy]

If you are searching column B (and it appears you are), then it will always
find cell B1 if there are no other entries that match. So you would change
your code to something like this:

Sub FindCustomer() 'This selects customer from customer
Dim rng As Range

' Sheets("Customer").Select
Range("B1").Select
Set rng = Columns(2).Find(What:=ActiveCell & "*", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
If rng.Address = "$B$1" Then
MsgBox "Not Found"
Else
rng.Select
End If
Else
MsgBox "Not Found"
End If
End Sub