InputBox - finding the input in a range

[ManhattanRebel]

I am prompting the user for a numeric value with an inputbox. Here:
Answer = Application.InputBox(Prompt:="What is the Number?", Type:=1)

I then use cells.find command to search a range (column) for the numeric
value.
Here:
Cells.Find(What:="Answer", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

It searches the range, but it returns the error "Object variable not set."
However, if I had used the "SET" command for the variable "Answer", the type
wouldn't match, because that requires a "range" type variable, not a numeric
value.

 

[Rick Rothstein \(MVP - VB\)]

What happen if you remove the quote marks from around the word Answer in the
What parameter (that is, specify the contents of the variable and not the
piece of text "Answer")?

Rick

 

[OssieMac]

Basically it is failing because it cannot find the string Answer. You have
used Answer as a variable so don't use the double quotes in the find.

Cells.Find(What:=Answer, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

You should realize that you will always get an error in the above code if
the value is not found. You need to use set then test the set variable for
Not Nothing as follows.

Sub test()
Dim Answer
Dim foundAnswer

Answer = Application.InputBox _
(Prompt:="What is the Number?", Type:=1)

Set foundAnswer = Cells.Find(What:=Answer, _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not foundAnswer Is Nothing Then

MsgBox "Value of foundAnswer is " _
& foundAnswer.Value

MsgBox "Address of foundAnswer is " _
& foundAnswer.Address
Else
MsgBox "Value " & Answer & " Not found"
End If

End Sub