In this lesson you can learn how to find the row number of a matching value. This page explains about 3 ways to do this. You can adapt a solution according to your preference. These techniques will be very helpful when you develop applications like data entry systems. Because in these types of applications you may need to show the matching result to the user. For example you might want to let the user search with a text using a textbox or a inputbox.

I will use this sample worksheet to explain these three methods.

Suppose user search for the code “C00KLCMU14”. So the program should return the matching row number as 14.

**Method 1**

'Row number of matching value

Sub MatchingRowNumber()

Dim WS As Worksheet

Dim MatchingRow As Long

Dim i As Long

Dim SearchedValue As String

Set WS = Worksheets("Sheet1")

SearchedValue = "C00KLCMU14"

For i = 1 To 10000

If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then

MatchingRow = i

Exit For

End If

Next i

MsgBox MatchingRow

End Sub

In the above macro the For Next loop is used assuming there are 10,000 rows of data. But if you have data up to x rows then you should replace For i = 1 to 10000 with For i = 1 to x

If no match is found, the message box will output 0 because zero is the default value of the Long data type.

However there is one problem with this solution. It is that if you have data in a higher number of rows in your Excel sheet then the program will take considerable time to output the result. But we can avoid this problem if we use arrays.

**Method 2**

Assume we have one million rows of data in our sheet. Here is how you can use an array to get the required result without delay.

Sub MatchingRowNumber_ArraySolution()

Dim WS As Worksheet

Dim MatchingRow As Long

Dim i As Long

Dim SearchedValue As String

Dim DataArr() As Variant

Set WS = Worksheets("Sheet1")

DataArr = WS.Range("A1:E1000000")

SearchedValue = "C00KLCMU14"

For i = 1 To 1000000

If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then

MatchingRow = i

Exit For

End If

Next i

MsgBox MatchingRow

End Sub

This is an easy way to write a range to an array.

DataArr = WS.Range("A1:E1000000")

Things you need to remember when creating an array using the above method.

- This method always creates a multidimensional array.
- Indexes of both dimensions will start from 1.

Want to learn more about arrays? Check these lessons.

Multidimensional Arrays in VBA

Quick Multidimensional Array from Excel Range

Quickly Write Multidimensional Array to Excel Range

So far we have discussed two methods of finding the row number of a matching value. But sometimes cells can contain spaces before or after the values. So if there are spaces like that, then the program will not be able to find the matching value. To avoid this issue we can use the Trim function to remove unwanted spaces before or after the values.

This is how you can use the Trim function for the first method.

Replace

If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(WS.Range("B" & i).Value), SearchedValue, vbTextCompare) = 0 Then

And here is how you can use the Trim function for the second method.

Replace

If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(DataArr(i, 2)), SearchedValue, vbTextCompare) = 0 Then

Also if you get the SearchedValue from the user from a method such as input box or textbox then you can use the Trim function to remove unwanted spaces from SearchedValue as well.

If StrComp(Trim(DataArr(i, 2)), Trim(SearchedValue), vbTextCompare) = 0 Then

**Method 3**

Now let’s look at the third method to find the row number of a matching value. You can use the Find method to return the row number of the matching value.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet

Dim MatchingRow As Long

Dim SearchedValue As String

Set WS = Worksheets("Sheet1")

SearchedValue = "C00KLCMU14"

MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _

LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Row

MsgBox MatchingRow

End Sub

This will output the correct row number if there is a match. However if there is no match then the program will throw an error.

To avoid this error, we can use the error handling technique as follows. So if there is no match, the program will output 0.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet

Dim MatchingRow As Long

Dim SearchedValue As String

Set WS = Worksheets("Sheet1")

SearchedValue = "C00KLCMU14"

On Error Resume Next

MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _

LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Row

If Err.Number <> 0 Then

If Err.Number = 91 Then

MatchingRow = 0

Else

MsgBox "Unknown Error"

On Error GoTo 0

Exit Sub

End If

End If

On Error GoTo 0

MsgBox MatchingRow

End Sub

However this method will not work if cells contain extra spaces before or after the values.