Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox - finding the input in a range
I am prompting the user for a numeric value with an inputbox. He
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. He 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox - finding the input in a range
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 "ManhattanRebel" wrote in message ... I am prompting the user for a numeric value with an inputbox. He 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. He 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox - finding the input in a range
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 -- Regards, OssieMac "ManhattanRebel" wrote: I am prompting the user for a numeric value with an inputbox. He 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. He 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
put inputbox input into a cell | Excel Programming | |||
Check on the input in an inputbox | Excel Programming | |||
Numerical Input via an inputbox method | Excel Programming | |||
Numerical Input via an inputbox method | Excel Programming | |||
Check on the input in an inputbox | Excel Programming |