Message for Rick E or Juan Pablo or anyone mad enough to tackle the problem

Posted by Andy Evans on November 13, 2001 12:59 PM

Dear Rick Have tried the code which sort of works except that the routine ends with the column selected not the activecell of the record that was changed.
I have cobbled this code together which works as long as one of the cells is 'in' otherwise it crashes.

Sub find()
With Worksheets(1).Range("b1:B10")
Set c = .find("in")
If c = "in" Then
Range(c.Address).Select
End If
End With
End Sub

I tried changing it to this but it got worse (guess I'm barking up the wrong tree)

Sub find()
With Worksheets(1).Range("b1:B10")
Set c = .find("in")
If c = "in" Then
Range(c.Address).Select
Else
If c = "out" Then
MsgBox ("nope")
End If
End If
End With
End Sub

Posted by Joe Was on November 13, 2001 2:58 PM

Try this code!

This will only find: as case sensitive and only find the first value, even if rerun! If "in" is not in the list it will display your MsgBox. JSW

Sub find()
With Worksheets(1).Range("b1:B10")
Set c = .find("in")
If c = "in" Then
Range(c.Address).Select
Else
MsgBox ("nope")
End If
End With
End Sub

Posted by Ivan F Moala on November 13, 2001 6:48 PM

Your code will crash out because you have set
c to an object which when exercuted will return
either another object or nothing. If it returns
nothing (ie. nothing was found) then it assumes
that c was not set as c then = Nothing and errors out. To get over this;
Try

Sub find()
Dim c
With Worksheets(1).Range("B1:B10")
On Error Resume Next
Set c = .find("in")
If Not c Is Nothing Then
Range(c.Address).Select
Else
MsgBox "in not found!"
End If
On Error GoTo 0
Set c = Nothing
End With

Ivan

Posted by Rick E on November 14, 2001 10:00 AM

Sorry, did not keep the code.

Can you post my original code in reply to me? Then maybe I could help. GOing to lunch, back in two hours.