Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

This blog entry shows many examples, such as…
• Include a default entry in an InputBox.
• Trap the Cancel and OK buttons in an InputBox.
• Test to find an InputBox entry in a worksheet cell.
• Show the row, colunn number, and column letter of the found item.
• Show the absolute and relative address of the found item.

In the first picture, the worksheet holds a table of names. You want to search for a particular name, and if that name exists by itself in a cell, you want to know that found cell’s location information.



In the next picture, the macro (posted below) is attached to a Forms button. The InputBox appears with a default entry to clue the user as to what kind of entry should be attempted.



In this last picture, an entry is made (Tom Urtis in this case) and a Message Box provides the found cell’s location information.



Here’s the macro, with explanatory notes in green font.

Sub FindRowColumnAddress()

'Declare a String type variable for the InputBox entry.
Dim strName$
'Define the strName variable as an InputBox.
'The default entry of John Doe help the user understand
'what kind of entry to type in.
strName = _
InputBox("Enter the name to locate:", "Name finder", "John Doe")

'Open a Case structure to test for a True statement.
Select Case True

'StrPtr can tell you if the InputBox was cancelled.
Case StrPtr(strName) = 0
MsgBox "You clicked Cancel.", 48, "Entry cancelled."
Exit Sub

'Nothing entered but OK clicked.
Case Len(strName) = 0
MsgBox "You clicked OK but entered nothing.", _
48, "Can't locate nothing."
Exit Sub

'Something was entered. Now does it exist on the worksheet?
Case Else

'Declare a Variant type variable to test the Find function.
'Declare a Long type variable for the found row number.
'Declare a Long type variable for the found column number.
'Declare a String Type variable to show the found column letter.
'Declare a String type variable to show the found address.
Dim varFind As Variant
Dim varFindRow As Long, varFindColumn As Long
Dim strAddressAbsolute As String, strAddressRelative As String
Dim strColumnLetter As String

'Test for the entry in the InputBox being found.
Set varFind = _
ActiveSheet.UsedRange.Find _
(What:=strName, LookIn:=xlFormulas, LookAt:=xlWhole)

'The entry, by itself (xlWhole), was not found in a worksheet cell.
If varFind Is Nothing Then
MsgBox "''" & strName & "'' was not found.", 48, "No such animal."

'The entry was found by itself, so here are the entry's location info.
Else
varFindRow = varFind.Row
varFindColumn = varFind.Column
strAddressAbsolute = Cells(varFindRow, varFindColumn).Address
strAddressRelative = Cells(varFindRow, varFindColumn).Address(0, 0)
strColumnLetter = Split(strAddressAbsolute, "$")(1)

'Message Box informs the user of the found location info.
MsgBox "''" & strName & "'' was found!!!." & vbCrLf & vbCrLf & _
"Row: " & varFindRow & vbCrLf & _
"Column number: " & varFindColumn & vbCrLf & _
"Column letter: " & strColumnLetter & vbCrLf & _
"Address (absolute): " & strAddressAbsolute & vbCrLf & _
"Address (relative): " & strAddressRelative, , "Location info..."

'Release system memory for the Variant object.
Set varFind = Nothing

End If

'Close the Case structure.
End Select

End Sub