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.

## How to Find Exact Match Using VBA: 5 Easy Ways

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

### Method-1: Find 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”*.

**Steps:**

- Firstly, press
**Alt+F11**on your keyboard. After that, it will open the**Visual Basic Editor.** - Next, click on
**Insert > Module.**

- After that, 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
```

- 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. - Then, save it and click on
**F5**to run the VBA code.

- Finally, you will get the following
**Message Box**containing the cell position of the student named**Joseph Michael**.

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

### Method 2: Find 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:**

- Firstly, follow
**Step-01**of**Method-01**. - Secondly, write down 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,**“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. - After that, save it and click on
**F5**to run the VBA code.

- As a result, you will get the new student’s name as
*“Henry Jackson”*.

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

**Steps:**

- Firstly, follow
**Step-01**of**Method-01.** - Secondly, 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. - Then, save it and click on
to run the VBA code.*F5*

- Finally, according to the case, the student’s name will be changed to
*“Henry Jackson”*.

### Method 4: Use InStr Function to Find the Exact Match

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

**Steps:**

- To begin with, follow
**Step-01**of**Method-01.** - Secondly, 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.
- Then, save it and click on
**F5**to run the VBA code.

- Therefore, you will get the
*“Passed”*status for the students who have passed.

### Method 5: Find Exact Match and Extract Data

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

**Steps:**

- To start with, follow
**Step-01**of**Method-01.** - Secondly, 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
```

- Therefore, 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**.- Then, save it and click on
**F5**to run the VBA code.

- Lastly, you will get the following extracted data for the students having the name
.*Michael James*

**Download Practice Workbook**

You can download the free practice Excel template from here.

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

- 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

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

Sheet1withSheet2I will have the matched texts besides the Existing column.In

Sheet1I have a range of texts and after the comparison, I will have matched texts in the Similar Text column.The comparison will be done with

Sheet2To 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