How to Find Exact Match Using VBA in Excel (5 Easy Ways)

If you are looking for some of the easiest ways to find an exact match using VBA, then you will find this article useful. So, let’s get started with the article and get to know the ways of finding an exact match.

How to Find Exact Match Using VBA: 5 Easy Ways

I have used the following table which has the records of the results of some students. I will explain different ways to find the exact match by using this table with the help of VBA.

For this purpose, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Find Exact Match in a Range of Cells

If you want to find an exact match of a string like the name of a student and then find the cell position of this student then you can do this by following this method.

Here, I am going to find an exact match for the student named “Joseph Micahel”.

Steps:

• Firstly, press  Alt+F11  on your keyboard. After that, it will open the Visual Basic Editor.
• Next, click on Insert > Module.

• After that, type the following code in the editor.

Sub searchtxt()
Dim rng As Range
Dim str As String
Set rng = Sheets("exact match").Range("B5:B10").Find("Joseph Michael", LookIn:=xlValues)
If Not rng Is Nothing Then
str = rng.Address
MsgBox (rng & " in " & str)
End If
End Sub

• Here, “exact match” is the sheet name and “B5:B10” is the range of students’ names, and “Joseph Michael” is the student’s name which is to be found out.
• rng is declared as a range object and str as a string variable to store the address of the searched item.
• The IF statement will assign the item’s address to the str variable.
• Then, save it and click on   F5  to run the VBA code.

• Finally, you will get the following Message Box containing the cell position of the student named Joseph Michael.

Read More: Excel VBA: Find the Next Empty Cell in Range

Method 2: Find Exact Match and Replace it with VBA

I will show you how to find the indicated student’s name and then replace it with another one because somehow this name has been mistakenly written here. You can find your desired string and replace it by following this method.

Steps:

• Firstly, follow Step-01 of Method-01.
• Secondly, write down the following code.

Sub FindandReplace()
Dim rng As Range
Dim str As String
With Worksheets("find&replace").Range("B5:B10")
Set rng = .Find("Donald Paul", LookIn:=xlValues)
If Not rng Is Nothing Then
str = rng.Address
Do
rng.Value = Replace(rng.Value, "Donald Paul", "Henry Jackson")
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
End Sub

• Here, “find&replace” is the sheet name and “B5:B10” is the range of students’ names, “Donald Paul” is the student’s name which is to be found out, and then “Henry Jackson” will be the student’s name instead of the previous one.
• WITH statement will avoid the repetition of the piece of code in every statement.
• The IF statement will assign the item’s address to the str variable and the DO loop will replace all occurrences of the search word.
• After that, save it and click on  F5  to run the VBA code.

• As a result, you will get the new student’s name as “Henry Jackson”.

Method 3: Find Exact and Case-Sensitive Match

If you want to find a case-sensitive match then follow this method. Here, I have two names similar to one another but there is a difference in the case and depending on the case I will replace the last student’s name.

Steps:

• Firstly, follow Step-01 of Method-01.
• Secondly, write down the following code.

Sub exactmatch()
Dim rng As Range
Dim str As String
With Worksheets("case-sensitive").Range("B5:B10")
Set rng = .Find("Donald Paul", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not rng Is Nothing Then
str = rng.Address
Do
rng.Value = Replace(rng.Value, "Donald Paul", "Henry Jackson")
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
End Sub

• Here, “case-sensitive” is the sheet name and “B5:B10” is the range of students’ names, and “Donald Paul” is the student’s name which is to be found out, and then “Henry Jackson” will be the student’s name instead of the previous one.
• WITH statement will avoid the repetition of the piece of code in every statement.
• The IF statement will assign the item’s address to the str variable and the DO loop will replace all occurrences of the search word.
• Then, save it and click on F5 to run the VBA code.

• Finally, according to the case, the student’s name will be changed to “Henry Jackson”.

Method 4: Use InStr Function to Find the Exact Match

Suppose, you want to match Pass or Fail to correspond to the names of the students depending on the Result column where Pass or Fail has been written. To find this string in the Result column and write down “Passed” in the Status column for the students who have passed the exam, you can use the InStr function.

Steps:

• To begin with, follow Step-01 of Method-01.
• Secondly, write down the following code.

Sub Checkstring()
Dim cell As Range
For Each cell In Range("C5:C10")
If InStr(cell.Value, "Pass") > 0 Then
cell.Offset(0, 1).Value = "Passed"
Else
cell.Offset(0, 1).Value = " "
End If
Next cell
End Sub

• Here, the cell range is C5:C10 which is the Result column
• InStr(cell. value, “Pass”) > 0 is the condition where the number is greater than zero (when the cell contains “Pass”)  then the following line will continue and give the output in the adjacent cell as Passed.
• If the condition becomes false means a cell doesn’t contain any “Pass” then the line under ELSE will execute and give the output value in the adjacent cell as Blank.
• This loop will continue for each cell.
• Then, save it and click on  F5  to run the VBA code.

• Therefore, you will get the “Passed” status for the students who have passed.

Method 5: Find Exact Match and Extract Data

If you want to extract the corresponding data for a student named “Michael James” then you can follow this method.

Steps:

• To start with, follow Step-01 of Method-01.
• Secondly, write down the following code.

Sub Extractdata()
Dim lastusedrow As Long
Dim i As Integer, icount As Integer
lastusedrow = ActiveSheet.Range("B100").End(xlUp).Row
For i = 1 To lastusedrow
If InStr(1, Range("B" & i), "Michael James") > 0 Then
icount = icount + 1
Range("E" & icount & ":G" & icount) = Range("B" & i & ":D" & i).Value
End If
Next i
End Sub

• Therefore, I have used B100 as the Active Sheet Range (you can use any range according to your use).
• InStr(1, Range(“B” & i), “Michael James”) > 0 is the condition for checking if the cell in column B contains Michael James.
• Range(“E” & icount & “:G” & icount) is the range where you want your output data and Range(“B” & i & “:D” & i).value will give the values from the column B to D.
• Then, save it and click on  F5  to run the VBA code.

• Lastly, you will get the following extracted data for the students having the name Michael James.

Download Practice Workbook

You can download the free practice Excel template from here.

Conclusion

In this article, I tried to cover the easiest ways to find an exact match using VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Further Readings

• FindNext Using VBA in Excel
• How to Find Blank Cells Using VBA in Excel
• Excel VBA to Find Matching Value in Column
• How to Find Last Row Using Excel VBA
• Find Last Row with Data in a Range Using Excel VBA Macros
• Excel VBA to Find Multiple Values in Range
• Excel VBA to Find Value in Column