Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,939
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 84
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace - delete the remainder of the text in the cell after my Find [email protected] Excel Programming 4 August 4th 07 03:39 AM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Despite data existing in Excel 2002 spreadsheet Find doesn't find AnnieB Excel Discussion (Misc queries) 1 June 16th 06 02:15 AM
find and delete text, find a 10-digit number and put it in a textbox Paul Excel Programming 3 November 16th 04 05:21 PM
backwards find function to find character in a string of text Ashleigh K. Excel Programming 1 January 14th 04 05:36 PM


All times are GMT +1. The time now is 01:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"