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
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.
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.
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
With
And here is how you can use the Trim function for the second method.
Replace
With
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.
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.
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.
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.