The VBA Find function searches for a specific value within a range of cells in Excel and returns the first matching cell as a Range object. Unlike Excel’s Ctrl+F, VBA Find enables automation, allowing you to programmatically locate and manipulate data based on criteria like value, case, or search direction.
Syntax of VBA Find Function
Below is the syntax given for using the find function in VBA,
RangeObject.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) As Range
Parameters:
| Parameter | Required | Description |
|---|---|---|
| What | Required | The value to search for (e.g., text, number). |
| After | Optional | The cell after which to start searching (e.g., Range("A3")). |
| LookIn | Optional | Where to search: xlValues (cell values), xlFormulas (formulas), xlComments (comments). |
| LookAt | Optional | Match type: xlWhole (entire cell content) or xlPart (part of cell content). |
| SearchOrder | Optional | Search by: xlByRows (row-wise) or xlByColumns (column-wise). |
| SearchDirection | Optional | Direction: xlNext (forward) or xlPrevious (backward). |
| MatchCase | Optional | Case-sensitive search: True or False (default). |
| MatchByte | Optional | For double-byte languages (e.g., Japanese): True (match double-byte only), False (match single-byte). |
| SearchFormat | Optional | Custom format to match (e.g., bold text; requires Application.FindFormat). |
Steps to Use the VBA Find Function in Excel
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,
