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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.