Cells.Find woe

[Jess Wundring]

I've been trying to get the following code to work in Excel 2003. It's fine
if X is found. It breaks on the X assignment statement if Cells.Find fails to
find anything.

The error is *always*: Object variable or With block variable not set
(Error 91)

I've tried it with and without using the "set" in front of the X assignment,
with and without declaring DIM X, alternately as Object and as Range

Range("A1").Select
x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate


If (x Is Nothing) Then
MsgBox "Not Found"
Else
MsgBox "x is " & x
End If


So, while it works okay with the "On Error Resume Next" statement inserted,
I must be doing something wrong for it to generate a stop execution
error...I'd like to find out what is the proper way of doing this. Can anyone
give me a clue?

Thanks

[Norman Jones]

Hi Jess,

Try:

'=============>>
Public Sub Tester()
Dim Rng As Range
Const strSearch As String = "abc"

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

If Not Rng Is Nothing Then
MsgBox strSearch & " found at " & Rng.Address(0, 0)
Else
MsgBox strSearch & " not found"
End If

End Sub
'<<=============


---
Regards,
Norman


"Jess Wundring" <[email protected]> wrote in message
news:[email protected]...
> I've been trying to get the following code to work in Excel 2003. It's
> fine
> if X is found. It breaks on the X assignment statement if Cells.Find fails
> to
> find anything.
>
> The error is *always*: Object variable or With block variable not set
> (Error 91)
>
> I've tried it with and without using the "set" in front of the X
> assignment,
> with and without declaring DIM X, alternately as Object and as Range
>
> Range("A1").Select
> x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=False).Activate
>
>
> If (x Is Nothing) Then
> MsgBox "Not Found"
> Else
> MsgBox "x is " & x
> End If
>
>
> So, while it works okay with the "On Error Resume Next" statement
> inserted,
> I must be doing something wrong for it to generate a stop execution
> error...I'd like to find out what is the proper way of doing this. Can
> anyone
> give me a clue?
>
> Thanks

[JE McGimpsey]

One way:

Dim x As Range
Set x = Cells.Find( _
What:=",", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not x Is Nothing Then
MsgBox "x is " & x.Value
Else
MsgBox "Not Found"
End If

The problem you're having is that you're trying to activate a
non-existent range.


In article <[email protected]>,
"Jess Wundring" <[email protected]> wrote:

> I've been trying to get the following code to work in Excel 2003. It's fine
> if X is found. It breaks on the X assignment statement if Cells.Find fails to
> find anything.
>
> The error is *always*: Object variable or With block variable not set
> (Error 91)
>
> I've tried it with and without using the "set" in front of the X assignment,
> with and without declaring DIM X, alternately as Object and as Range
>
> Range("A1").Select
> x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=False).Activate
>
>
> If (x Is Nothing) Then
> MsgBox "Not Found"
> Else
> MsgBox "x is " & x
> End If
>
>
> So, while it works okay with the "On Error Resume Next" statement inserted,
> I must be doing something wrong for it to generate a stop execution
> error...I'd like to find out what is the proper way of doing this. Can anyone
> give me a clue?
>
> Thanks

[Jess Wundring]

Thanks Norm and JE but I've tried it with

Dim x as Object
and
Dim x as Range
and without any dim statement whatsoever

I've tried it for each of the above cases with

Set x = Cells.Find....
and just plain old
x = Cells.Find....

all of these cases give me error 91 when the Cells.Find statement fails to
find an instance of "What".

Am I missing something? The only diff I see in Norm's example is that he
assigns the What to a string var. And I see no diff in JE's example, unless
you think it might be my variable names?

Anyway....Thanks for responding you guys. I really appreciate it. Hope
Microsoft fixes their Help system for Office 12. It's so f****g broken right
now I want to scream whenever I try to use it. Sometimes I actually do
scream.


"Jess Wundring" wrote:

> I've been trying to get the following code to work in Excel 2003. It's fine
> if X is found. It breaks on the X assignment statement if Cells.Find fails to
> find anything.
>
> The error is *always*: Object variable or With block variable not set
> (Error 91)
>
> I've tried it with and without using the "set" in front of the X assignment,
> with and without declaring DIM X, alternately as Object and as Range
>
> Range("A1").Select
> x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=False).Activate
>
>
> If (x Is Nothing) Then
> MsgBox "Not Found"
> Else
> MsgBox "x is " & x
> End If
>
>
> So, while it works okay with the "On Error Resume Next" statement inserted,
> I must be doing something wrong for it to generate a stop execution
> error...I'd like to find out what is the proper way of doing this. Can anyone
> give me a clue?
>
> Thanks

[Jess Wundring]

AHA! I just didn't see it.

I needed to eliminate the .Activate method on the Cells.Find......

THANK YOU BOTH (especially JE) for pointing this out to me.

You're awesome!



> "Jess Wundring" wrote:
>
> > I've been trying to get the following code to work in Excel 2003. It's fine
> > if X is found. It breaks on the X assignment statement if Cells.Find fails to
> > find anything.
> >
> > The error is *always*: Object variable or With block variable not set
> > (Error 91)
> >
> > I've tried it with and without using the "set" in front of the X assignment,
> > with and without declaring DIM X, alternately as Object and as Range
> >
> > Range("A1").Select
> > x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas,
> > LookAt:= _
> > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False _
> > , SearchFormat:=False).Activate
> >
> >
> > If (x Is Nothing) Then
> > MsgBox "Not Found"
> > Else
> > MsgBox "x is " & x
> > End If
> >
> >
> > So, while it works okay with the "On Error Resume Next" statement inserted,
> > I must be doing something wrong for it to generate a stop execution
> > error...I'd like to find out what is the proper way of doing this. Can anyone
> > give me a clue?
> >
> > Thanks