VBA Find Function in Excel
Last Updated : 08 Dec, 2023
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
|
| 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,
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'.