How to Use the Find Function in VBA (6 Examples)

One of the most important and widely used functions that we use in Visual Basic Application (VBA) is the Find function. In this article, I’ll show you how you can use the FIND function of Excel with proper examples and illustrations.

Download Practice Workbook

6 Quick Examples to Use the FIND Function in VBA

Here we’ve got a data set with the Book Names, Authors and Prices of some books of a bookshop called Martin Bookstore.

Today our objective is to see various types of uses of the Find function of VBA from this data set.

1. Use the Find Function in VBA with No Parameter

You can use the Find function of VBA with no parameters.

Then it will search for a specific value within a range of cells and return the first match it finds.

Let’s search for the name “P. B. Shelly” in the column Author (C4:C13).

The line with the Find function will be:

The complete VBA code will be:

⧭ VBA Code:

Sub Find()

Set cell = Range("C4:C17").Find("P. B. Shelly")

MsgBox cell.Address

End Sub

⧭ Output:

It produces a Macro called Find. If you run the Macro, it will return $C$6, the first cell address with the name P. B. Shelly.

Read more: Find within a Range with VBA in Excel: Including Exact and Partial Matches

2. Apply the Find Function in VBA with the After Parameter (To Start Searching from a Specific Cell)

You can use the After parameter with the Find function in VBA. Then it will start searching for a value from below a cell within a range.

For example, let’s start searching for the name “P. B. Shelly” from below cell C6.

The line of code will be:

And the complete VBA code will be:

⧭ VBA Code:

Sub Find()

Set cell = Range("C4:C13").Find("P. B. Shelly", After:=Range("C6"))

MsgBox cell.Address

End Sub

⧭ Output:

It will return $C$13 because it starts searching from below cell C6, that’s from cell C7. So it gets P. B. Shelly in cell C13 first.

Read more: How to Find String in a Cell Using VBA in Excel

3. Run the Find Function in VBA with Wrapping around the After Parameter (To Search for a Value in a Circular Way)

The After parameter with the Find function searches for a value in a circular way

That is, it starts searching from below a cell within a range, finishes searching in the range, and then again starts from the top of the range.

For example, let’s start searching the name “John Keats” from below cell C8 using the After parameter.

The line of code will be:

And the complete VBA code will be:

⧭ VBA Code:

Sub Find()

Set cell = Range("C4:C13").Find("John Keats", After:=Range("C8"))

MsgBox cell.Address

End Sub

⧭ Output:

It will return $C$7 because it starts searching from below cell C8, that’s from cell C9.

It doesn’t find anything up to cell C13, so it again starts from cell C4 and finds one in cell C7.

Similar Readings:

• How to Find String with VBA in Excel (8 Examples)
• Find Exact Match Using VBA in Excel (5 Ways)
• How to Find and Replace Using VBA (11 Ways)

4. Utilize the Find Function in VBA with the LookAt Parameter (For Exact or Partial Match)

You can use the Find function in VBA with the LookAt parameter.

Use LookAt = xlWhole for an Exact match, and LookAt=xlPart for a Partial match.

For example, let’s try to find out if there is any book with the name “Ode” in the Book Name column (B4:B13).

If we use xlWhole in the line of code:

The output will show an error because it won’t find any exact match.

But if you use:

Then it’ll return $B$9, because there is a book with the name “Ode” in it in cell B7, Ode to the Nightingale.

So the complete code for the partial match is:

⧭ VBA Code:

Sub Find()

Set cell = Range("B4:B13").Find("Ode", LookAt:=xlPart)

MsgBox cell.Address

End Sub

5. Operate the Find Function in VBA with SearchDirection Parameter (To Specify the Direction of the Search)

You can also use the Find function in VBA with the SearchDirection parameter.

Use SearchDirection = xlNext  for searching from Top to Bottom.

And SearchDirection = xlPrevious for searching from Bottom to Top.

For example, let’s try to find the Author Elif Shafak in the column Author (C4:C13).

If we use xlNext in the line of code:

Then it will return $C$5.

But if you use:

Then it’ll return $C$11.

So the complete code for searching from bottom to top  is:

⧭ VBA Code:

Sub Find()

Set cell = Range("C4:C13").Find("Elif Shafak", SearchDirection:=xlPrevious)

MsgBox cell.Address

End Sub

6. Find Function in VBA with MatchCase Parameter (For Case-Sensitive or Insensitive Match)

Finally, you can use the Find function with the MatchCase parameter.

Use MatchCase = True for a case-sensitive match, and MatchCase=False for a case-insensitive match.

For example, let’s try to find the book “mother” in the Book Name column (B4:B13).

If we use True in the line of code:

The output will show an error because it won’t find any match.

But if you use:

Then it’ll return $B$9 because there is a book named “Mother” in cell B8.

So the complete code for the case-insensitive match is:

⧭ VBA Code:

Sub Find()

Set cell = Range("B4:B13").Find("Mother", MatchCase:=False)

MsgBox cell.Address

End Sub

Conclusion

Using these methods, you can use the Find Function in Visual Basic Application. Do you have any questions? Feel free to ask us.

Related Readings

• How to Find Substring Using VBA (9 Ways)
• VBA Find Last Row in Excel (5 ways)
• VBA Find in Column in Excel (7 Approaches)
• FindNext Using VBA in Excel (2 Examples)