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.

**Table of Contents**hide

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

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

**â§**** 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:

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

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

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

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

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

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