How to Find Exact Match Using VBA in Excel (5 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.

Download Workbook

5 Ways to Find Exact Match Using VBA

I have used the following table which has the records of 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 versions according to your convenience.

Method-1: Finding 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”.

Step-01:
➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.

➤Go to Insert Tab>> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

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.

➤Press F5

Result:
After that, you will get the following Message Box containing the cell position of the student named “Joseph Michael”.

Read more: Find within a Range with VBA in Excel: Including Exact and Partial Matches

Method-2: Finding Exact Match and Replacing it Using VBA

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

Step-01:
➤Follow Step-01 of Method-1
➤Type 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, 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.

➤Press F5

Result:
After that, you will get the new student’s name as “Henry Jackson”.

Method-3: Finding 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.

Step-01:
➤Follow Step-01 of Method-1
➤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.

➤Press F5

Result:
Now, according to the case, the student’s name will be changed to “Henry Jackson”.

Similar Readings:

• How to Find String in a Cell Using VBA in Excel (2 Methods)
• VBA Find in Column in Excel (7 Approaches)
• How to Find String with VBA in Excel (8 Examples)

Method-4: Using InStr function 

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.

Step-01:
➤Follow Step-01 of Method-1
➤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.

➤Press F5

Result:
Then, you will get the “Passed” status for the students who have passed.

Method-5: Finding Exact Match and Extracting Data

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

Step-01:
➤Follow Step-01 of Method-1
➤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

Here, 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.

➤Press F5

Result:
Afterward, you will get the following extracted data for the students having the name Michael James.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

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

• VBA Find Last Row in Excel (5 ways)
• How to Use the Find Function in VBA (6 Examples)
• FindNext Using VBA in Excel (2 Examples)
• How to Find Substring Using VBA (9 Ways)