Cells.Find woe
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" wrote in message
...
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
|