Solved: If condition for Find cell value

[DB_Mike]

I am trying to search for text in a spreadsheet that may or may not be present and delete it if it is found. I tried the following which does not work,

[vba]
If Cells.Find(What:="PRICES", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate _
Then
Range(ActiveCell, ActiveCell.Offset(3, 0)).Select
Selection.ClearContents
End If
[/VBA]
Can anyone suggest an alternative approach?

Thanks for the help.

Mike

[DB_Mike]

sorry - my attempt at using the VBA code identifier didn't work very well.

never mind - I didn't realize I could fix it.

[OBP]

Mike, I never use the built in Find, I always use a VBA constructed search, but it does strike me that maybe your code needs to have a range selection in it somewhere so that the "Find" function knows where to look for "PRICES".
Did you try recording a Macro of doing the "Find" manually?

[OBP]

For Example I get this -
Range("A1:A7").Select
Selection.Find(What:="for", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

[DB_Mike]

I did record the find command to get the complete syntax. I realized from your comment that I didn't have a search range selected at the time the command is being evaluated so I established the range before the If statement but it still errors.

I will try to find another way to accomplish this. What is the VBA constructed search that you referred to?

Mike

[OBP]

The VBA method uses a For/next, for/each loop or with/end with using the selection to test each cell in your column for the required "word". There are already a few examples on here, you could try a search for "Find Text" etc.
If you don't find what you want can you post the workbook and we will construct something for you.

[OBP]

Here is one
http://vbaexpress.com/forum/showthre...ight=Find+text
and this one
http://vbaexpress.com/forum/showthre...ight=Find+text
and
http://vbaexpress.com/forum/showthre...ight=Find+text

[mdmackillop]

[VBA]Set c = Cells.Find(What:="PRICES")
If Not c Is Nothing Then
Range(c, c.Offset(3, 0)).ClearContents
End If
[/VBA]

[DB_Mike]

Thanks to both of you. The 2nd solution with code from Zack solved my problem but I appreciate the responses.