If no FIND value is found, then a Msgbox to say so and exit sub
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 |
If no FIND value is found, then a Msgbox to say so and exit sub
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 |
If no FIND value is found, then a Msgbox to say so and exit sub
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 wrote in message ... 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 |
All times are GMT +1. The time now is 03:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com