Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If find function to not find anything
Hi,
I basically want to run an if function that searches a range to find a variable. (hopefully not finding it) then it will run the code. if it does find a match I want it to just post a msgbox. What code syntax do i need for find nothing. Sub test () S = 10 Worksheets("sheet1").Range("A1:A30").Select Cells.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If find Is Nothing Then ' run my code Else ' msgbox. end if end sub Thanks for any help Carl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If find function to not find anything
Sub test () S = 10 set SearchRange = Worksheets("sheet1").Range("A1:A30") set c = SearchRange.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If c Is Nothing Then ' run my code Else ' msgbox. end if end sub "Carlos" wrote: Hi, I basically want to run an if function that searches a range to find a variable. (hopefully not finding it) then it will run the code. if it does find a match I want it to just post a msgbox. What code syntax do i need for find nothing. Sub test () S = 10 Worksheets("sheet1").Range("A1:A30").Select Cells.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If find Is Nothing Then ' run my code Else ' msgbox. end if end sub Thanks for any help Carl |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If find function to not find anything
You should probably drop the After:=ActiveCell from the code. If the search
range is not on the active sheet then your code will bomb. Since all you are doing is checking for the existence of S it does not matter where you start... -- HTH... Jim Thomlinson "Joel" wrote: Sub test () S = 10 set SearchRange = Worksheets("sheet1").Range("A1:A30") set c = SearchRange.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If c Is Nothing Then ' run my code Else ' msgbox. end if end sub "Carlos" wrote: Hi, I basically want to run an if function that searches a range to find a variable. (hopefully not finding it) then it will run the code. if it does find a match I want it to just post a msgbox. What code syntax do i need for find nothing. Sub test () S = 10 Worksheets("sheet1").Range("A1:A30").Select Cells.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If find Is Nothing Then ' run my code Else ' msgbox. end if end sub Thanks for any help Carl |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If find function to not find anything
Thanks Jim and Joel
I'll give it a go! Cheers carl "Jim Thomlinson" wrote: You should probably drop the After:=ActiveCell from the code. If the search range is not on the active sheet then your code will bomb. Since all you are doing is checking for the existence of S it does not matter where you start... -- HTH... Jim Thomlinson "Joel" wrote: Sub test () S = 10 set SearchRange = Worksheets("sheet1").Range("A1:A30") set c = SearchRange.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If c Is Nothing Then ' run my code Else ' msgbox. end if end sub "Carlos" wrote: Hi, I basically want to run an if function that searches a range to find a variable. (hopefully not finding it) then it will run the code. if it does find a match I want it to just post a msgbox. What code syntax do i need for find nothing. Sub test () S = 10 Worksheets("sheet1").Range("A1:A30").Select Cells.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If find Is Nothing Then ' run my code Else ' msgbox. end if end sub Thanks for any help Carl |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If find function to not find anything
Thanks boths,
Works great but did need to the remove the after:=activeCell. Cheers Carl "Joel" wrote: Sub test () S = 10 set SearchRange = Worksheets("sheet1").Range("A1:A30") set c = SearchRange.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If c Is Nothing Then ' run my code Else ' msgbox. end if end sub "Carlos" wrote: Hi, I basically want to run an if function that searches a range to find a variable. (hopefully not finding it) then it will run the code. if it does find a match I want it to just post a msgbox. What code syntax do i need for find nothing. Sub test () S = 10 Worksheets("sheet1").Range("A1:A30").Select Cells.Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If find Is Nothing Then ' run my code Else ' msgbox. end if end sub Thanks for any help Carl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |