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.

Data Set to Use the Find Function in VBA

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:

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

The complete VBA code will be:

VBA Code:

Sub Find()

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

MsgBox cell.Address

End Sub

VBA Code to Use the Find Function in VBA

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.

Output of VBA Find Function with no Parameter

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:

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

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.

Output of Using the Find Function in VBA

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:

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

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.

Output of Using the Find Function in VBA


Similar Readings:


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:

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

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

Error While Using the Find Function in VBA

But if you use:

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

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

VBA Code to Use the Find Function in VBA


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:

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

Then it will return $C$5.

Output of Using the Find Function in VBA

But if you use:

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

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

VBA Code to Use the Find Function in VBA


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:

Set cell = Range("B4:B13").Find("mother", MatchCase:=True)

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

Error While Using the Find Function in VBA

But if you use:

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

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

Output of Using the VBA Code in Excel

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

Tags:

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo