PDA

View Full Version : Solved: If condition for Find cell value



DB_Mike
07-12-2006, 11:59 AM
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,


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

Can anyone suggest an alternative approach?

Thanks for the help.

Mike

DB_Mike
07-12-2006, 12:00 PM
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
07-12-2006, 12:21 PM
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
07-12-2006, 12:25 PM
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
07-12-2006, 12:58 PM
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
07-12-2006, 01:16 PM
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
07-12-2006, 01:20 PM
Here is one
http://vbaexpress.com/forum/showthread.php?t=8610&highlight=Find+text
and this one
http://vbaexpress.com/forum/showthread.php?t=7851&highlight=Find+text
and
http://vbaexpress.com/forum/showthread.php?t=6519&highlight=Find+text

mdmackillop
07-12-2006, 01:53 PM
Set c = Cells.Find(What:="PRICES")
If Not c Is Nothing Then
Range(c, c.Offset(3, 0)).ClearContents
End If

DB_Mike
07-12-2006, 02:15 PM
Thanks to both of you. The 2nd solution with code from Zack solved my problem but I appreciate the responses.