If Then using "Find" command


O

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
 
Ad

Advertisements

J

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
 
O

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
 
G

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)
 
G

Gary Keramidas

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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top