PDA

View Full Version : Solved: Find and select a cell based on user input



John8669
07-06-2008, 12:16 PM
I am trying (unsucessfully) to create a macro that opens an InputBox and allows the user to enter a document name. Then I want to select the cell in a single column that contains that input. There are no duplicates.

Thanks

mikerickson
07-06-2008, 12:45 PM
This might work for you.
Sub test()
Dim userInput As String
Dim rowFound As Variant
Dim dataColumn As Range

Set dataColumn = ThisWorkbook.Sheets("sheet1").Range("A:A"): Rem adjust

Do
userInput = Application.InputBox(userInput & "Which document do you want?", Type:=2)
If userInput = "False" Then Exit Sub: Rem cancel pressed

rowFound = Application.Match(userInput, dataColumn, 0)

If IsError(rowFound) Then userInput = "That document not found. Try again." & vbCr
Loop Until Right(userInput, 1) <> vbCr

dataColumn.Parent.Activate
dataColumn.Cells(rowFound, 1).Select
End Sub

John8669
07-06-2008, 01:03 PM
Thanks for the help...

It does not seem to find the document, which is a number not text, when I change "Dim userInput As String" to "Dim userInput As Integer" I get "Type Mismatch" on this line

If userInput = "False" Then Exit Sub: Rem cancel pressed

mikerickson
07-06-2008, 01:13 PM
The looping structure et.al is dependent on userInput being a string. Leave userInput as string and change

rowFound = Application.Match(Val(userInput), dataColumn, 0)

John8669
07-06-2008, 01:18 PM
Works Like A Charm!!!!

Thanks Mike....

mikerickson
07-06-2008, 01:27 PM
You're welcome.

mdmackillop
07-06-2008, 02:30 PM
Hi John,
If your question is answered, please mark it solved using the Tread Tools dropdown
Regards
MD