Using a finds results in an if statement

[woodyinoz]

Hi all,

I've got the following code which performs a find on a worksheet looking for a value held in the variable strCont:

    Cells.Find(What:=(strCont), After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

If the search comes back having not found anything I don't want a message to appear on the screen, I want to be able to use the 'not found' in an If statement. i.e. If not found then do something else.

How do I do this guys?

Cheers

Woody

 

[PHV]

Dim r As Range
Set r = Cells.Find(What:=(strCont), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If r Is Nothing Then
' do something else
Else
r.Activate
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

there is a very good example of exactly this in the VBA help files under the FIND method - please try looking there in the 1st instance

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question