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.
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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address
  1. Wayne says:

    I found this very useful for data I have, however, what if the name I am seeking is in more than one cell reference. Can the code be adjusted to return multiple cells?

    • Tom Urtis says:

      Yes, the code can be adjusted. It would help you to limit (if you need to) what area of the spreadsheet you want to search, such as a particular column or range, unless you want to search the entire sheet. Once you specify that, I can post an example that would match your situation.

  2. PAUL E LEWIS says:

    This code has been very helpful to me but it doesn’t go quite far enough for what I need to do. Once the target text is located I would like to activate the cell that the text is located iithun so that I can delete that entire row. I’m not a VBA programmer so your help will be very appreciated. thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *