ExcelBanter - If find function to not find anything

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - If find function to not find anything (https://www.excelbanter.com/excel-programming/406045-if-find-function-not-find-anything.html)

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

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

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

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

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