Pages

Get Row Number of a Matching Value

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.

Sample excel sheet with data

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

Program will show the row number of the matching value in a message box

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.

Program will throw an error if can’t find a match

Clicking debug will highlight the line where error occurred

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.