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

Suppose you have the following dataset:

Method 1 – Find an Exact Match in a Range of Cells

Steps:

• Press Alt+F11 on your keyboard to open the Visual Basic Editor.
• Click on Insert > Module.

• 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

“exact match” is the sheet name and “B5:B10” is the range of cells to search

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.

• Save and press F5 to run the VBA code.

• A message Box will open containing the cell position of the exact match (Joseph Michael in this example).

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

Method 2 – Find an 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:

• Press Alt+F11 on your keyboard to open the Visual Basic Editor.
• Click on Insert > Module.
• Type the following code in the editor.

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

“find&replace” is the sheet name and “B5:B10” is the range of cells to search.

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

• Save and press F5 to run the VBA code.

All cells with the required information should now be changed.

Method 3 – Find an Exact and Case-Sensitive Match

Steps:

• Press Alt+F11 on your keyboard to open the Visual Basic Editor.
• Click on Insert > Module.
• Type the following code in the editor.

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

“case-sensitive” is the sheet name and “B5:B10” is the range of cells to search.

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

• Save and press F5 to run the VBA code.

All cells with the required information and case should now be changed.

Method 4 – Use the InStr Function to Find an Exact Match

Steps:

• Press Alt+F11 on your keyboard to open the Visual Basic Editor.
• Click on Insert > Module.
• Type the following code in the editor.

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

The cell range (C5:C10) is the Result column

InStr(cell. value, “Pass”) > 0 is the condition where, if 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 (a cell doesn’t contain “Pass”) then the line under ELSE will execute and give the output value in the adjacent cell as Blank.

• Save and press F5 to run the VBA code.

All cells with the required information should show the proper output.

Method 5 – Find an Exact Match and Extract Data

Steps:

• Press Alt+F11 on your keyboard to open the Visual Basic Editor.
• Click on Insert > Module.
• Type the following code in the editor.

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

InStr(1, Range(” ” & i), ” “) > 0 is the condition to check if the cell in the appropriate column (B) contains the search data (Michael James).

Range(” ” & icount & “: ” & icount) is the range where you want your output data (E to G) and Range(” ” & i & “: ” & i).value is the data source (B to D).

• Save and press F5 to run the VBA code.

The extracted data should be shown in the chosen cell range.

Download Practice Workbook

You can download the free practice Excel template from here.

Related Articles:

• 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