Finding a text in the active worksheet

[arbo80]

Hi,

I have the code below to find a text in a worksheet. It works when the text is there but it gives me the following error message when the text doesn't exit. Runtime error 91 object variable not set. Can somebody please help me?

Sub test()

Dim res As Boolean
Dim item As String


item = "Ca "

res = Cells.Find(What:=item, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
If ((res) And Len(item) = 3) Then
MsgBox "Found!!" & nitem
Else
MsgBox "not found"
End If

End Sub

thanks,
A

 

[PHV]

Dim myRange As Range
Set myRange = Cells.Find(What:=item, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False)
If Not myRange Is nothing Then
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Sub test()

Dim res As RANGE
Dim item As String


item = "Ca "

Set res = Cells.Find(What:=item, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
        SearchFormat:=False)
If (([b]not res is nothing[/b]) And Len(item) = 3) Then
    MsgBox "Found!!" & nitem
Else
    MsgBox "not found"
End If

End Sub

Skip,

_[red][/red]

 

[arbo80]

Thanks guys! It works fine.