If Then using "Find" command

[ogopogo5]

I'm trying to make the following VB work.

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

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

The first If Then works as there is a "110". However on the second IF
Then there is no "115" and I get a "Run-Time Error 91". Is there a way
to make the second IF Then work, without getting the "Run-Time Error"?

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

You need to use a range object to hold the cell found (or not found)

dim rngFound as range

set rngfound = Cells.Find(What:="110", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt:= xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False , SearchFormat:=False)
If not rngfound is nothing Then
rngfound .select
End If

set rngfound = nothing

set rngfound = Cells.Find(What:="115", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt:= xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False , SearchFormat:=False)
If not rngfound is nothing Then
rngfound .select
End If

 

[ogopogo5]

Hi Jim Thank you very much for the quick reply. I copied and pasted and
I noticed the following is all in red. I get a "compile Error : Syntax
Error" message.

set rngfound = Cells.Find(What:="110", After:=ActiveCell,
LookIn:=xlFormulas, _ LookAt:= xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False , SearchFormat:=False)

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gary Keramidas]

the newsgroups wrap text. try this

Set rngfound = Cells.Find(What:="110", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

 

[Gary Keramidas]

after you paste it, put the cursor at the end of the first line and press
delete, it should work then.

 

[ogopogo5]

Hi Gary

I got it working, thank you very much for your help.

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***