how do i prevent code interruption

[Luis939]

I have a big dilemma guys, whenever I want to use the find method to look for a specific string that isnt there, I get an error when the code runs and the program doenst continue executing as intended. So if I have
Cells.Find(What:="Text", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate


but the spreadsheet doesnt have that text in the file so I get an error. Is there any way so that i can say if the Find method doesnt find the word, then to simply continue executing the rest of the code.

 

[RobBroekhuis]

Don't try to find and activate in the same statement - if no cell is found, it can't be activated, hence the error. Use instead

dim cell as range
set cell=cells.find(....)
if not cell is nothing then
cell.activate
end if

Actually, usually there is no need to activate the cell - you can just use the range variable to do your job, e.g.

if not cell is nothing then
cell.interior.color=vbRed
end if
Rob

 

[Stretchwickster]

Your best bet is to write something like this:

Dim rangeFound as Range

Set rangeFound = Cells.Find(What:="Text", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
If Not rangeFound Is Nothing then
  'do what you wanna do
Else
  'do something different
End If

Clive

http://www.kucu.co.uk

 

[Stretchwickster]

Sorry...the "Set" line was meant to read as follows:
Set rangeFound = Cells.Find(What:="Text", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)


Clive

http://www.kucu.co.uk