Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

`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")

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. 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"))

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. ### 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"))

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

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:

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

Then it will return \$C\$5. 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)

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:

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

The output will show an error because it won’t find any match. 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. So the complete code for the case-insensitive match is:

VBA Code:

``````Sub Find()

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

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.  