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