How can I get around "Find" if there's nothing to find

[CarolineHedges]

I need to my macro to be able to Find certain words and delete the rows
wherever these words occur.

If I use the Cells.Find(????) to locate the words, the macro doesn't
work ifthe word to find isn't in the spreadsheet.

How can I get around the Cell.Find(????), ie to ignore and move on to
the net step if there aren't any more of ??? to find.

This is causing me so much frustration!!!

Thanks

 

[Die_Another_Day]

Set a range to the cell.find then check if it is nothing like this:
Dim FindCell as Range
Set FindCell = Cells.Find(What:="YourText", After:=Range("A1"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If FindCell = nothing then
Msgbox "Search String not Found"
Else
'Your Code here
End if

HTH

Die_Another_Day

 

[CarolineHedges]

Cool thanks!

 

[CarolineHedges]

I have input the code, but it doesn't like the bit:

If FindCell = Nothing Then

An error message comes up as "Invalid use of object".

It wont even run the macro with this part.... help!!

 

[Ardus Petus]

Typo!
If FindCell is Nothing

Et voilà!

Cheers,
--
AP

"CarolineHedges"
<[email protected]> a écrit dans
le message de (e-mail address removed)...

 

[CarolineHedges]

I have done this:

Dim FindCell As Range
Set FindCell = Cells.Find(What:="Security", After:=Range("A2"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If FindCell Is Nothing Then
GoTo 2
Else
Selection.EntireRow.Delete

End If

But it doesn't do anything. Sorry I'm not very advanced on macros, I've
taught myself over the last 10mnths!!

Do I need to give it a range? When you said set a range to the
cel.........

Thank you in advance

 

[Guest]

Your range FindCell is not selected. You are deleting whatever has been
selected which is not specified in the code you have posted here. you
probably want something more like...

Dim FindCell As Range
Set FindCell = Cells.Find(What:="Security", After:=Range("A2"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If FindCell Is Nothing Then
GoTo 2
Else
findcell.EntireRow.Delete

End If

 

[Die_Another_Day]

Hi Ardus, quick question. What's the difference between "is" and "="? I
mix those up frequently and am not sure when it's appropriate to use
them.

Tx

Die_Another_Day

 

[Guest]

Is referes to objects. = refers to variables. So for a range object or a
worksheet object you need is. For an Integer or a String you want =...

 

[Die_Another_Day]

Thanks Jim. That somewhat makes sense. Just a couple other questions...
Why can I still use if FindCell = ""? Do I just use "is" with the
"nothing" command?

Die_Another_Day

 

[NickHK]

Because the default property of the range object is ".Value". So you are
really comparing a string value and hence "=" not "Is" with code :
If FindCell.Value = ""....

I always explicitly state the property of an object, to avoid confusion as
above

NickHK

Thanks Jim. That somewhat makes sense. Just a couple other questions...
Why can I still use if FindCell = ""? Do I just use "is" with the
"nothing" command?

Die_Another_Day