Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


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.


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

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 Office 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. Click here to see how you can show 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 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.

Find string in column and Return Row Number Based on String

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

insert module

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

run VBA code

⧭ Output:

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

show row number based on 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.

Read More: Excel VBA: Return Row Number of Value (5 Suitable Methods)


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.

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

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

run VBA code

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

enter information in input box

⧭ Output:

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

show the row number based on 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.

Read More: How to Return Row Number of a Cell Match in Excel (7 Methods)


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.

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

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

Run VBA code

⧭ Output:

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

return row number

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

Read More: How to Get Row Number from Range with Excel VBA (9 Examples)


Similar Readings


4. Using Columns Property

Now, we will use 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.

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

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

run VBA code

⧭ Output:

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

find string and return row number

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

Read More: [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)


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.

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

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

run VBA code

⧭ Output:

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

return row number

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

Read More: VBA Range with Variable Row Number in Excel (4 Examples)


6. Applying VBA StrComp Function

Previously, you have seen that we use For loop to return row numbers, but now, we will use 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.

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

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

run VBA code

⧭ Output:

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

show the row number

🔎 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

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.

Read More: How to Find Row Number Using VBA in Excel (4 Macros)


Conclusion

That’s the end of today’s session. I strongly believe that from now, 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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo