Using FIND in Visual Basic
 

I am trying to use the Selection.Find in a macro, and want to know how to get
past the part when it cannot find a cell matching the criteria. It works fine
when it can find something, but when it does not find it, I get a Run-time
error 91 (Object variable or With Block variable not set). Does it have
something to do with the Activate part of my code? I am using the following:

Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

In other words, is there a way to ask it to find, but without activating? I
want it to do something else if it cannot find a cell matching the criteria.
Thanks in advance.

Using FIND in Visual Basic
 

Use a range object something like this...

dim rngFound as range

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

if rngfound is nothing then
msgbox "Not Found"
else
...

end if
--
HTH...

Jim Thomlinson


"Mike" wrote:

I am trying to use the Selection.Find in a macro, and want to know how to get
past the part when it cannot find a cell matching the criteria. It works fine
when it can find something, but when it does not find it, I get a Run-time
error 91 (Object variable or With Block variable not set). Does it have
something to do with the Activate part of my code? I am using the following:

Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

In other words, is there a way to ask it to find, but without activating? I
want it to do something else if it cannot find a cell matching the criteria.
Thanks in advance.

Using FIND in Visual Basic
 

thanks. I'll give that a shot.

"Jim Thomlinson" wrote:

Use a range object something like this...

dim rngFound as range

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

if rngfound is nothing then
msgbox "Not Found"
else
...

end if
--
HTH...

Jim Thomlinson


"Mike" wrote:

I am trying to use the Selection.Find in a macro, and want to know how to get
past the part when it cannot find a cell matching the criteria. It works fine
when it can find something, but when it does not find it, I get a Run-time
error 91 (Object variable or With Block variable not set). Does it have
something to do with the Activate part of my code? I am using the following:

Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

In other words, is there a way to ask it to find, but without activating? I
want it to do something else if it cannot find a cell matching the criteria.
Thanks in advance.