If no FIND value is found, then a Msgbox to say so and exit sub

[L. Howard Kittle]

Hello Excel users and experts,

Win XP, Excel 2002

A number is scanned into cell A1 and the below code is called through a
change event macro. It finds that value in range aList and a date stamp is
entered 2 columns to the right of the find value. This works fine.

However, if there is NOT a value found in aList, then for some reason the
date stamp is entered in first row of aList, 2 columns over. What would be
the code to prompt a message box "Not Valid No." when there is no find? I
can't figure out how to "catch" the fact that a number was not found.

Sub BookReturnDate()

Dim aList As Range
Dim Bcode As Variant
Bcode = Range("A1").Value
If Bcode = "" Then Exit Sub

Set aList = Range("G2:G2505")
On Error Resume Next

aList.Select
Selection.Find(What:=Bcode).Activate
ActiveCell.Offset(0, 2).Value = Now

'If no find then msgbox "Goof Up"

End Sub

Thanks for your help,
Regards,
Howard

 

[Chip Pearson]

Howard,

Try,

Dim FoundCell As Range
Set FoundCell = Selection.Find(....)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If

 

[pfsardella]

The alterations are untested.

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Sub BookReturnDate()

Dim aList As Range
Dim rngFind as Range
Dim Bcode As Variant

Bcode = Range("A1").Value
If Bcode = "" Then Exit Sub

Set aList = Range("G2:G2505")
On Error Resume Next

Set rngFind = aList.Find(What:=Bcode)
If not rngFind is Nothing Then
rngFind.Offset(0, 2).Value = Now
Else
msgbox "Goof Up"
End If

End Sub

 

[L. Howard Kittle]

Hi Paul,

Thanks a ton for the code advice. Pretty smooth. I did have to change it a
bit so the "Found" cell would be selected and brought into view. This is to
allow a visual verification that the date stamp is in the correct place.
(End users wishes) The Goof Up message box is the watchdog of that but...

Again, thanks a ton Paul, and Chip.
Regards,
Howard