How to Find a String in a Column and Return a Row Number Using VBA (6 Methods)

If you want to locate a specific string within a column and retrieve the corresponding row number using VBA code in Excel, follow these steps. Microsoft Visual Basic for Applications (VBA) is an event-driven programming language that allows you to automate tasks within Excel. Before proceeding, ensure that the Developer tab is visible on your ribbon. If it’s not, you can enable it through Excel.

Method 1 – Returning Row Number Based on String

  • Open the VBA Window:
    • Go to the Developer tab on the Excel ribbon.
    • Select Visual Basic from the Code group.
    • This opens the VBA editor window.

Find string in column and Return Row Number Based on String

  • Insert a Module:
    • In the VBA editor, go to the Insert tab.
    • Click on Module from the dropdown.
    • A new module will be created.

insert module

  • Enter VBA Code:
    • If the module isn’t already selected, click on it.
    • Add the following code:
Sub GetRowNumber_2()
Dim findName  As Range
Set findName = ActiveSheet.Cells.Find("Stuart")
If Not findName Is Nothing Then
MsgBox "Row Number: " & findName.Row
Else
MsgBox "Student not found!"
End If
End Sub
    • Save the code.
  • Run the VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8 to open the Macro dialog box.
    • Select GetRowNumber_2 from the list of macros.
    • Click Run.

run VBA code

  • Output:
    • You’ll see a message box displaying the row number based on the specified string.

show row number based on string

VBA Code Explanation

Sub GetRowNumber_2()

Defines the sub-procedure for the macro.

Dim findName  As Range

Declares the necessary variable.

Set findName = ActiveSheet.Cells.Find("Stuart")

Searches for the string “Stuart” within the active sheet.

If Not findName Is Nothing Then
MsgBox "Row Number: " & findName.Row
Else
MsgBox "Student not found!"
End If
  • If the string is found:
    • Displays a message box with the row number.
  • If the string is not found:
    • Displays a message box with the text “Student not found!”
End Sub

Ends the sub-procedure of the macro.

Read More: Excel VBA: Return Row Number of Value


Method 2 – Using Input Box

  • Open the VBA Window:
    • Go to the Developer tab on your Excel ribbon.
    • Select Visual Basic from the Code group.
    • This opens the Visual Basic for Applications (VBA) editor window.

Find string in column and return row number based by Using Input Box

  • Insert a Module:
    • In the VBA editor, go to the Insert tab.
    • Click on Module from the dropdown.
    • A new module will be created.

  • Enter VBA Code:
    • If the module isn’t already selected, click on it.
    • Add the following code:
Sub getrownumber_6()
Dim Student_Name As String
Dim row1 As Range
Student_Name = InputBox("What is Name?")
Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If row1 Is Nothing Then
MsgBox ("Student Not found")
Else
MsgBox (row1.row)
End If
End Sub
    • Save the code.
  • Run the VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8 to open the Macro dialog box.
    • Select GetRowNumber_6 from the list of macros.
    • Click Run.

run VBA code

  • Input Prompt:
    • A message box will appear, prompting you to enter the name of the student.
    • Click OK.

enter information in input box

  • Output:
    • You’ll receive a message box displaying the row number based on the entered student name.

show the row number based on string

VBA Code Explanation

Sub getrownumber_6()

Defines the sub-procedure for the macro.

Dim Student_Name As String
Dim row1 As Range

Declares the necessary variable to store the student’s name.

Student_Name = InputBox("What is Name?")

Displays an input dialog box for the user to enter the student’s name.

Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False)
  • Searches for the specified string (student name) within the cells.
  • If found, assigns the corresponding cell range to row1.
If row1 Is Nothing Then
MsgBox ("Student Not found")
Else
MsgBox (row1.row)
End If
  • If the student name is not found:
    • Displays a message box with the text “Student not found.”
  • If the student name is found:
    • Displays a message box with the row number where the name was located.
End Sub

Ends the sub-procedure of the macro.

Read More: How to Find Row Number Using VBA in Excel 


Method 3 – Use the Range.Find Method

  • Open the VBA Window:
    • Go to the Developer tab on your Excel ribbon.
    • Select Visual Basic from the Code group.
    • This opens the Visual Basic for Applications (VBA) editor window.

find string in column and return row number by Using Range.Find Method

  • Insert a Module:
    • In the VBA editor, go to the Insert tab.
    • Click on Module from the dropdown.
    • A new module will be created.

  • Enter VBA Code:
    • If the module isn’t already selected, click on it.
    • Add the following code:
Sub Return_Row()
Dim W1S As Worksheet
Dim Row_Match As Long
Dim Value_Search As String
Set W1S = Worksheets("Sheet1")
Value_Search = "Peter"
Row_Match = W1S.Cells.find(What:=Value_Search, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
MsgBox "Here,Row Number is: " & Row_Match
End Sub
    • Save the code.
  • Run the VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8 to open the Macro dialog box.
    • Select Return_Row from the list of macros.
    • Click Run.

Run VBA code

  • Output:
    • You’ll receive a message box displaying the row number based on the specified string (“Peter” in this example).

return row number

VBA Code Explanation

Sub Return_Row()

Defines the sub-procedure for the macro.

Dim W1S As Worksheet
Dim Row_Match As Long
Dim Value_Search As String

Declares the necessary variable for the worksheet.

Set W1S = Worksheets("Sheet1")

Specifies the worksheet where the search will occur.

Value_Search = "Peter"

Defines the value you’re searching for.

Row_Match = W1S.Cells.find(What:=Value_Search, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
  • Uses the Range.Find method to locate the value.
  • If the value is found:
    • Assigns the corresponding row number to Row_Match.
MsgBox "Here,Row Number is: " & Row_Match
  • Displays a message box with the row number.
End Sub

Ends the sub-procedure of the macro.

Read More: How to Get Row Number from Range with Excel VBA


Method 4 – Using Columns Property

  • Open the VBA Window:
    • The VBA (Visual Basic for Applications) window is where we’ll insert our code. To access it:
      • Go to the “Developer” tab on your ribbon.
      • Select “Visual Basic” from the “Code” group.

Using Columns Property to find string in column and return row number

  • Insert a Module:
    • VBA modules store code in the Visual Basic Editor.
    • To insert a module:
      • Go to the Insert tab in the VBA editor.
      • Click on Module from the drop-down menu.
      • A new module will be created.

  • Insert VBA Code:
    • If the module isn’t already selected, choose it.
    • Enter the following code within the module:
Sub Return_Row_1()
Dim Row_1 As Long
Row_1 = Columns(3).Find(What:="John").Row
MsgBox "Here,Row Number is: " & Row_1
End Sub
  • Save the code.
  • Run the VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8.
    • In the Macro dialog box, select Return_Row_1 as the macro name.
    • Click Run.

run VBA code

Output:

    • You’ll see a message box displaying the row number corresponding to the specified string (“John”).

find string and return row number

VBA Code Explanation

Sub Return_Row_1()

Subroutine is named for our macro.

Dim Row_1 As Long
  • We declare the necessary variable (Row_1) to store the row number.
Row_1 = Columns(3).Find(What:="John").Row

We find the row containing the string “John” within the third column.

MsgBox "Here,Row Number is: " & Row_1
  • The message box displays the row number.
End Sub

Ends the sub-procedure of the macro.


Method 5 – Utilizing VBA For Loop

  • Open the VBA Window:
    • The VBA (Visual Basic for Applications) window is where we’ll insert our code. To access it:
      • Go to the “Developer” tab on your ribbon.
      • Select “Visual Basic” from the “Code” group.

Utilizing VBA For Loop find string in column and return row number

  • Insert a Module:
    • VBA modules store code in the Visual Basic Editor.
    • To insert a module:
      • Go to the Insert tab in the VBA editor.
      • Click on Module from the drop-down menu.
      • A new module will be created.

  • Insert VBA Code:
    • If the module isn’t already selected, choose it.
    • Enter the following code within the module:
Sub Return_Row_2()
Dim ws1 As Worksheet
Dim Row_Match As Long
Dim k As Long
Dim Value_Search As String
Set ws1 = Worksheets("Sheet1")
Value_Search = "John"
For k = 1 To 100
If StrComp(ws1.Range("C" & k).Value, Value_Search, vbTextCompare) = 0 Then
Row_Match = k
Exit For
End If
Next k
MsgBox "Here,Row Number is: " & Row_Match
End Sub
    • Save the code.
  • Run VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8.
    • In the Macro dialog box, select Return_Row_2 as the macro name.
    • Click Run.

run VBA code

  • Output:
    • You’ll see a message box displaying the row number corresponding to the specified string (“John”).

return row number

VBA Code Explanation

Sub Return_Row_2()

The subroutine is named for our macro

Dim ws1 As Worksheet
Dim Row_Match As Long
Dim k As Long
Dim Value_Search As String

We declare necessary variables.

Set ws1 = Worksheets("Sheet1")

Specifies the worksheet where we’ll search.

Value_Search = "John"

In a later step, search for specific information in a range.

For k = 1 To 100
If StrComp(ws1.Range("C" & k).Value, Value_Search, vbTextCompare) = 0 Then
Row_Match = k
Exit For
End If
Next k
  • If the string “John” is found in column C, the corresponding row number is stored in Row_Match.
MsgBox "Here,Row Number is: " & Row_Match
  • The message box displays the row number.
End Sub

Ends the sub-procedure of the macro.


Method 6 – Applying VBA StrComp Function

  • Open the VBA Window:
    • VBA has its own separate window for working with code.
    • To open the VBA window:
      • Go to the Developer tab on your ribbon.
      • Select Visual Basic from the Code group.

Applying VBA StrComp Function to find string in column and return row number

  • Insert a Module:
    • VBA modules store code in the Visual Basic Editor.
    • To insert a module:
      • Go to the Insert tab in the VBA editor.
      • Click on Module from the drop-down menu.
      • A new module will be created.

  • Insert VBA Code:
    • If the module isn’t already selected, choose it.
    • Enter the following code within the module:

Sub Return_R0W_Array()

Dim Ws1 As Worksheet
Dim Row_match As Long
Dim k As Long
Dim Value_Search As String
Dim Data_array As Variant
Set Ws1 = Worksheets("Sheet1")
Data_array = Ws1.Range("A1:E100")
Value_Search = "John"
For k = 1 To 100
If StrComp(Data_array(k, 3), Value_Search, vbTextCompare) = 0 Then
Row_match = k
Exit For
End If
Next k
MsgBox "Here,Row Number is: " & Row_match
End Sub
    • Save the code.
  • Run VBA Code:
    • Close the Visual Basic window.
    • Press Alt+F8.
    • In the Macro dialog box, select Return_R0W_Array as the macro name.
    • Click Run.

run VBA code

  • Output:
    • You’ll see a message box displaying the row number corresponding to the specified string (“John”).

show the row number

VBA Code Explanation

Sub Return_R0W_Array()

The subroutine is named for our macro.

Dim Ws1 As Worksheet
Dim Row_match As Long
Dim k As Long
Dim Value_Search As String
Dim Data_array As Variant

We declare necessary variables.

Set Ws1 = Worksheets("Sheet1")

Specifies the worksheet where we’ll search.

Data_array = Ws1.Range("A1:E100")

Contains the range from which we’ll search for specific information.

Value_Search = "John"

In a later step, search for specific information in a range.

For k = 1 To 100
If StrComp(Data_array(k, 3), Value_Search, vbTextCompare) = 0 Then
Row_match = k
Exit For
End If
Next k

The loop iterates through the rows. If the string “John” is found in column C, the corresponding row number is stored in Row_match.

MsgBox "Here,Row Number is: " & Row_Match

The message box displays the row number.

End Sub

Ends the sub-procedure of the macro.


Download Practice Workbook

You can download the practice workbook from here:


 

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo