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

Suppose you have the following dataset:

Sample Data Set to Find Exact Match Using VBA


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.

Opening VBA Window

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

Applying VBA Code to Find Exact Match in a Range of Cells

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

Showing Final Result to Find Exact Match in a Range of Cells

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.

Sample Data Set

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.

Applying VBA Code to Find Exact Match and Replace

All cells with the required information should now be changed.

Showing Final Result


Method 3 – Find an Exact and Case-Sensitive Match

 

Sample Data set

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.

Applying VBA Code to Find Exact and Case-Sensitive Match

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

Showing Final Result to Find Exact and Case-Sensitive Match


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

 

Sample Data Set

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.

Employing VBA Code to Use InStr Function for Finding the Exact Match

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

Showing Final Result


Method 5 – Find an Exact Match and Extract Data

Sample Data Set

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.

Applying VBA Code to Find Exact Match and Extract Data

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

Showing Final Result to Find Exact Match and Extract Data


Download Practice Workbook

You can download the free practice Excel template from here.


 


Related Articles:

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

2 Comments
  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

    • Hello Chandrakant, regarding your problem I have created the following two datasets where in both sheets I have your predefined text 762-V-231. After comparing the range of texts from Sheet1 with Sheet2 I will have the matched texts besides the Existing column.
      In Sheet1 I have a range of texts and after the comparison, I will have matched texts in the Similar Text column.
      dataset1
      The comparison will be done with Sheet2
      dataset2
      To do this comparison you can use the following code

      Sub find_text()
      Dim source_txt As Range, find_txt As Range
      For Each source_txt In Sheets(“Sheet1”).Range(“A2:A6”)
      For Each find_txt In Sheets(“Sheet2”).Range(“A2:A6”)
      If InStr(1, source_txt, find_txt, vbTextCompare) > 0 Then
      source_txt.Offset(0, 1) = find_txt
      Exit For
      End If
      Next
      Next
      Set source_txt = Nothing
      Set find_txt = Nothing
      End Sub

      After pressing F5, you will have the following result
      result

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo