Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colum is not found thats missing a column B how to find it | Excel Worksheet Functions | |||
FIND function does not return ZERO when text is not found | Excel Worksheet Functions | |||
How to find a row since I found a value in a column? see example | Excel Worksheet Functions | |||
Find text in another workbook and paste if found match - VBA | Excel Discussion (Misc queries) | |||
Copy Rows found using Find All feature | Excel Discussion (Misc queries) |