VBA Find Function in Excel

In an Excel sheet subset of cells represents the VBA Range which can be single cells or multiple cells. The find function will help to modify our search within its Range object. A specific value in the given range of cells is to search with the help of the Find function. Excel VBA provides different parameters in the Find function so that we can search according to the search order or direction and also we can make a case-sensitive search which will be discussed further in this article.

Excel VBA Find Function

As we know about the Find Function in Excel, we can use the Shortcut key “Ctrl+F” to find the content that you are looking for. The FIND function of VBA searches for a specified value in the range that is defined by the user. A VBA code is written by entering arguments of the FIND function.

Below is the syntax given for using the find function in VBA,

Syntax: Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection AsXlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat]) As Range

Parameter	Required	Description
What	Required	The value for which we are searching
After	Optional	Range of cells from where the search will start
Lookin	Optional	Function search in value, formulas, and comment
LookAt	Optional	We can search in two ways one to search in a part of the string in a cell or to match the entire cell
SearchOrder	Optional	It can search in the cells row-wise, column-wise, or by the method
SearchDirection	Optional	We can mention whether to search forward or backward in the sheet
MatchCase	Optional	Case Sensitive should be considered or not should be mentioned by True or False
MatchByte	Optional	It can be used if we have used double – byte then we can assign True to match double-byte characters with double-byte only and false to match the double-byte characters with single-byte only
SearchFormat	Optional	The search format

How to Use the VBA Find Function in Excel Example

Use the Find Function without Parameter

Below are some Examples of VBA Find Function:

How to Use the Find Function without Parameter

Let’s take a sample of data. Below is the dataset given:

The following code is to find the name Niladri from cell A1 to A5 and the find function will return A3 in output which is the address of the cell.

1. Using After 

In this example, we are instructing the compiler to search for “Utsav” after cell A3.

2. Using LookIn 

LookIn function search in value, formulas, and comment.

value: It searches for the complete value in the cell if we are searching for “orange” then the ranged cell should contain the complete value “orange”.

formulas: It searches for the value which can be generated by any formula or function like left(“orange”,4)  will return or if it is present in any one of the ranged cells then it will return the address of that cell.

comment: It that whether the searched value contains in the comment box or not.

Let’s take a sample of data,

Code with three different parameters of LookIn.

3. Using LookAt

In this function, we have two parameters one is xlWhole and xlPart.

xlWhole: Searched value should be in the entire range.

xlPart: Searched value should have to match with a part of the cell.

Let’s take a sample of data,

Code to search the word “Tube” in the whole part of the cell.

Code to search the word “Tube” in some part of the cell.

4. Using SearchOrder

With the help of this function, we can tell VBA to search according to row or column. Let’s take a sample of the data.

Code to search “Ayush” row-wise and column-wise.

5. Using SearchDirection

With the help of this function, we can tell VBA to search forward or backward, like if we want to search in A1:A5 and we are using xlNext then VBA will search in the following order ⇢ A1, A2, A3, A4, A5. If we are using xlPrevious then VBA will search in the following order ⇢ A5, A4, A3, A2, A1. Let’s take a sample of data:

To Search “Ayush” using xlNext the code will return A4 and by using xlPrevious it will return A6.

6. Using MatchCase

This function tells VBA whether to be case sensitive (i.e. to differentiate between capital letters and small letters) or not if MatchCase is true then VBA will consider case sensitivity and if it is false then it will not consider case sensitivity. Let’s take a sample of data:

If we want to search for “Ayush” and MatchCase is true then it will return A6 and if MatchCase is false then it will return A4.

7. Using WildCard

The “*” symbol is used to represent more than one character. For example, “A*” VBA will search for a word that starts with A, “*a” VBA will search for a word that ends with a. Let’s take a sample of data:

The following code is to search for A1 and A2,

FAQs on VBA Find Function in Excel

What is the Find Function of VBA Excel?

VBA Find Function is a feature in Excel’s Visual Basic that helps the user to search for the range of value that are specified by the user in the Parameters in large datasets and automating tasks.

How to use the VBA Find Function in Excel?

Open the Visual Basic for Application editor in Excel. Then, you can write the VBA code using the ‘Find’ method to search for the desired data. Specify the Search parameters such as the value to find, the range to search within, and other optional settings like case sensitivity and search direction.

Is the VBA Find Function case-sensitive by default?

No, the VBA Find Function is not case-sensitive by default. You can make it case-sensitive by setting the ‘MatchCase’ parameter to ‘True’.