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.

VBA find exact match

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.

VBA find exact match

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

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

finding exact match

Then, the Visual Basic Editor will open up.

➤Go to Insert Tab>> Module Option

finding exact match

After that, a Module will be created.

finding exact match

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.

finding exact match

➤Press F5

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

finding exact match

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.

VBA find exact match

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.

find and replace

➤Press F5

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

find and replace

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.

VBA find exact match

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.

case-sensitive match

➤Press F5

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

case-sensitive match


Similar Readings:


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.

VBA find exact match

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.

using InStr function

➤Press F5

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

using InStr function

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.

VBA find exact match

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.

exact match and extract data

➤Press F5

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

exact match and extract data

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.

practice

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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

1 Comment
  1. I am trying to compare 2 strings using either instr or strcomp function in vba the string is something like this say 762-V-231 compare from one sheet with other sheet it does not work
    Can you help in this

Leave a reply

ExcelDemy
Logo