Excel VBA: Find String in Column and Return Row Number

If you are looking for special tricks to know how to find a string in a column and return the row number using VBA code in Excel, you’ve come to the right place. There are numerous ways to find a string in a column and return the row number using Excel VBA code. This article will discuss the details of these methods to find a string in a column and return the row number using VBA code. Let’s follow the complete guide to learn all of this.

Excel VBA to Find String in Column and Return Row Number: 6 Suitable Examples

In the following section, we will use six effective and tricky methods to find a string in a column and return the row number using VBA code in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft 365 version here, but you can utilize any other version according to your preference.

1. Return Row Number Based on String

If you want to find a string in a column and return the row number using VBA code in Excel, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language. To use this feature you first need to have the Developer tab showing on your ribbon. If you don’t see it, you can use the Excel Options to display the Developer tab on your ribbon. Once you have that, follow these detailed steps to find a string in a column and return the row number using the VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. After that, press Alt+F8.
When the Macro dialogue box opens, select GetRowNumber_2 in the Macro name. Click on Run.

⧭ Output:

Therefore, you will get the following message box which shows the row number based on the string.

🔎 VBA Code Explanation

Sub GetRowNumber_2()

First of all, provide a name for the sub-procedure of the macro.

Dim findName  As Range

Next, declare the necessary variable for the macro.

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

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

If Not findName Is Nothing Then
MsgBox "Row Number: " & findName.Row
Else
MsgBox "Student not found!"
End If

In the first line of this piece of the code, we inserted the If statement. In this If statement, if the first statement is true, we will get a message box with a row number; otherwise, we will only get a message box with the message “Students not found.”

End Sub

Finally, end the sub-procedure of the macro.

2. Using Input Box

Now, we will use the Input Box function which is useful for obtaining a single input from the user. Here, we will show another VBA code to find a string in a column and return the row number using the input box by applying the VBA code in Excel. You have to follow these detailed steps to find a string in a column and return the row number using the input box by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. After that press Alt+F8.
When the Macro dialogue box opens, select getrownumber_6 in the Macro name. Click on Run.

Therefore, a message box will appear where you have to enter the name of the student.
Click on OK.

⧭ Output:

Therefore, you will get the following message box which shows the row number based on the string.

🔎 VBA Code Explanation

Sub getrownumber_6()

First of all, provide a name for the sub-procedure of the macro.

Dim Student_Name As String
Dim row1 As Range

Next, declare the necessary variable for the macro.

Student_Name = InputBox("What is Name?")

Gives the user a dialog box to enter information, then returns the data they have entered.

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

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

False, SearchFormat:=False)

After that, specify the cell position where the input information from the input box will be selected.

If row1 Is Nothing Then
MsgBox ("Student Not found")
Else
MsgBox (row1.row)
End If

In the first line of this piece of the code, we inserted the If statement. A message box with the message “Students not found” will appear if the first statement is true; otherwise, a message box with the row number will appear.

End Sub

Finally, end the sub-procedure of the macro.

3. Use of Range.Find Method

Previously, you have seen that using the input box we get the information of the cell and return row number, but, now we will use Range.Find method to specify specific information and finally, we will get the row number based on that information. You have to follow these detailed steps to find a string in a column and return the row number using the Range.Find Method by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. After that, press Alt+F8.
When the Macro dialogue box opens, select Return_Row in the Macro name. Click on Run.

⧭ Output:

Therefore, you will get the following message box which shows the row number based on the string.

🔎 VBA Code Explanation

Sub Return_Row()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

Set W1S = Worksheets("Sheet1")

Later, specify the worksheet.

Value_Search = "Peter"

The row number that will be returned is contained in this variable.

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

After that, specify the cell position where the input information will be selected.

MsgBox "Here,Row Number is: " & Row_Match

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

4. Using Columns Property

Now, we will use the Columns property by which we will indicate the columns in the range of cells from the worksheet. Then, we will show how to return the row number using the columns property by applying the VBA code in Excel. You have to follow these detailed steps to find a string in a column and return the row number using the columns property by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. After that press Alt+F8.
When the Macro dialogue box opens, select Return_Row_1 in the Macro name. Click on Run.

⧭ Output:

As a result, you will get the following message box that shows the row number based on the string.

🔎 VBA Code Explanation

Sub Return_Row_1()

First of all, provide a name for the sub-procedure of the macro.

Dim Row_1 As Long

Next, declare the necessary variable for the macro.

Row_1 = Columns(3).Find(What:="John").Row

This variable will contain the number of the row of the range.

MsgBox "Here,Row Number is: " & Row_1

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

5. Utilizing VBA For Loop

Now, we will VBA For loop to find a string in a column and return the row number. You have to follow these detailed steps to find a string in a column and return the row number by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. After that press Alt+F8.
When the Macro dialogue box opens, select Return_Row_2 in the Macro name. Click on Run.

⧭ Output:

Therefore, you will get the following message box which shows the row number based on the string.

🔎 VBA Code Explanation

Sub Return_Row_2()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

Set ws1 = Worksheets("Sheet1")

Later, specify the worksheet.

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

In the first line of this piece of the code, we inserted the For statement. Now, starts looping from the declared column. If the iteration variable finds the word “John” in any row of the declared column, then it returns the row number.

MsgBox "Here,Row Number is: " & Row_Match

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

6. Applying VBA StrComp Function

Previously, you have seen that we used the For loop to return row numbers, but now, we will use the VBA StrComp function to do the same task. You have to follow these detailed steps to find a string in a column and return the row number by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

⧭ Insert Module:

VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

As a result, a new module will be created.

⧭ Insert VBA Code:

Now select the module if it isn’t already selected. Then write down the following code in it.

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

Next, save the code.

⧭ Run VBA Code:

Afterward, close the Visual Basic window. Afterward, press Alt+F8.
When the Macro dialogue box opens, select Return_ROW_Array in the Macro name. Click on Run.

⧭ Output:

Consequently, you will see the following message box displaying the row number based on the string.

🔎 VBA Code Explanation

Sub Return_R0W_Array()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

Set Ws1 = Worksheets("Sheet1")

Later, specify the worksheet.

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

The range from which specific information will be searched is contained in this variable.

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

MsgBox "Here,Row Number is: " & Row_Match

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Find String in Column and Return Row Number.xlsm

Conclusion

That’s the end of today’s session. I strongly believe that from now on, you may how to find a string in a column and return the row number using VBA code in Excel. If you have any queries or recommendations, please share them in the comments section below. We will get back to you as soon as possible.

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA: Find String in Column and Return Row Number

Excel VBA to Find String in Column and Return Row Number: 6 Suitable Examples

1. Return Row Number Based on String

2. Using Input Box

3. Use of Range.Find Method

4. Using Columns Property

5. Utilizing VBA For Loop

6. Applying VBA StrComp Function

Conclusion

Leave a reply Cancel reply