Excel VBA Find

The Find function is commonly used in VBA. It is a member of Range. The Find function is used to search a range of cells that contain a value or format.

The Find function is the same as using the Find Dialog in an excel worksheet. The shortcut key Ctrl + F will find the word or value, which we want to search in the entire sheet as well as the whole workbook.

Syntax

In VBA we write a function to find the word or content which we are looking for, such as:

To view the Find dialog box, go to the Home ribbon and click on the Find & Select in the Editing section.

After click on the Find & Select option, it displays the following dialog box.

Here is the explanation of the VBA Find function syntax.

What: What we are searching for. We need to mention that particular content we are searching for.

After: After which cell we want to search for.

Look In: Where to look for the thing we are searching for. Such as Values, Formulas, and Comments. Parameters are xlFormulas, xlValues, and xlComments.

Look At: Whether we are searching for the full content or only the part of the content. Parameters are xlWhole, and xlPart.

SearchOrder: Which order we are looking for, whether rows or columns. Parameters are xlByRows or xlByColumns.

SearchDirection: Which direction we are looking for, whether a next cell or previous cell. Parameters are xlByColumn or xlByRows.

MatchCase: The searching content is case sensitive or not. Parameters are True or False.

MatchByte: It is only used for double-byte language. Parameters are True or False.

SearchFormat: If we are searching by formatting, then we need to use the Application.FindFormat method.

How to Use Find Function

Follow some steps to learn how to use the Excel VBA Find function through the following examples.

Example 1: Suppose we have the data of the student’s reports that include their names. And we try to find the name Nick Jones as follows:

Step 1: First open Visual Basic and add a new module.

Step 2: Double click on the newly added module and write the code.

Step 3: Find function is a part of the Range property. So, we need to provide the range first. In this example, the range is from A2 to A12.

Step 4: After providing the range, write a dot, and add the Find function.

Step 5: Now, you can see the Find property in the pop-up table.

Step 6: Select the Find property option and open the bracket.

Step 7: Now, you can see the syntax of the Find function.

Step 8: We pass the argument such as What:= and This will help us to identify which parameter referring to.

Step 9: Now select that word, then write the dot and pass the argument Select.

Step 10: After selecting the Select argument, execute the code by using the Run button.

Step 11: It highlights the word “Tom Mustaine” as an output of the code in the Excel worksheet, as shown in the below screenshot.

Example 2: In this example, the Find function is used to search various characters in the text string “New Text “. And the formula is used to find the New Text using Find function.

The Find function is case-sensitive. So it gives the different results for the lower and upper case values such as “T” and “t“, which are in cells B2 and B3.

In cell B5, the argument[start_num] is set 5. That’s why the search starting from the fifth character of the text string. So the Find function gives priority to the second “e” substring.