Find Method not working

D

doodle

Greetings all.
Windows xp
xl97

Someone please explain to me why this code won't work.

Private Sub cmdViewOrder_Click()
myOrder = InputBox(Prompt:="Please enter the Order # that you would
like to view.")
Worksheets("Data").Range("B5:B65536").Find (myOrder)
End Sub

I am getting "Find Method of Range Class Failed"


Thanks in advance,

doodle
 
D

Don Guillett

You omitted a few things

Private Sub cmdViewOrder_Click()
myOrder = InputBox(Prompt:="Please enter the Order # that you would ike to
view.")
x= Worksheets("Data").Range("B5:B65536").Find (myOrder).address
msgbox x
End Sub
 
D

doodle

Don,

the code you posted is returning:

Runtime error 1004
Unable to get the find property of the range class.


doodle
 
D

Don Guillett

I just tested and it worked just fine. I used a regular sub in a regular
module with my data.Feel free to send me the workbook, if desired.

Sub findnum()
myOrder = InputBox(Prompt:="Please enter the Order # that you would like to
view.")

x = Worksheets("sheet6").Range("a1:a65536").Find(myOrder).Address
MsgBox x
End Sub
 
D

Don Guillett

I just re-tested and you need to move to regular sub instead of
Private Sub cmdFindOrder_Click()
OR
in the properties of the command button, change the focus to FALSE. Just one
of the reasons that I NEVER use these. TOO much hassle. Use a button from
the forms toolbar.

BTW. It DID work fine in xl2002 without the change.
 
D

Dave Peterson

If you typed something into the inputbox that wasn't found, then x will be
nothing and "msgbox x" will return an error.

Private Sub cmdViewOrder_Click()
dim myOrder as string
dim FoundCell as range

myOrder _
= InputBox(Prompt:="Please enter the Order # that you would ike to view.")

if trim(myorder) = "" then
exit sub
end if

set foundcell = Worksheets("Data").Range("B5:B65536").Find(myOrder)

if foundcell is nothing then
msgbox myorder & " was not found
else
msgbox foundcell.address
end if

End Sub
 
Top