FIND Function of VBA Excel

For example, the following code looks for the string “rose” in the range A1:A10 of “sheet1.”

With Sheets(“Sheet1”).Range(“A1:A10”)

Set Rng = .Find(What:=“rose”)

The purpose of using the FIND function in VBA is to locate the desired value in a given dataset. With a VBA code, one can automate the task of finding values in Excel. Similar to the VBA FIND, there is a “find and replace” feature in Excel too. Let us revisit the latter to understand the former.

A Revisit to the “Find and Replace” Feature of Excel

In this section, the “find and replaceFind And ReplaceFind and Replace is an Excel feature that allows you to search for any text, numerical symbol, or special character not just in the current sheet but in the entire workbook. Ctrl+F is the shortcut for find, and Ctrl+H is the shortcut for find and replace.read more” dialog box of Excel has been explained briefly. The steps to find and replace a value in a worksheet are listed as follows:

Step 1: Press the keys “Ctrl+F” together to access the “find and replace” feature of Excel. Alternatively, from the “editing” group of the Home tab, click the “find & select” drop-down. Next, select the option “find.”

Step 2: The “find and replace” dialog box appears, as shown in the following image. Click “options” to see more features.

Step 3: The succeeding dialog box is displayed. This box helps find the value specified in the “find what” box. The search is subject to the following constraints:

  • Within: This determines whether the search would be conducted in a worksheet or workbook.Search: This decides whether the search would be conducted in rows or columns.Look in: This decides whether the search would be conducted in formulas, values or comments of Excel.

At any point of time, one can click “options” to go back to the window shown in step 2.

Step 4: Click the “replace” option in the “find and replace” dialog box. The “replace with” option appears, as shown in the following image. This option is used when one value needs to be replaced by another.

This is how the “find and replace” feature of Excel works. Let us now return to the FIND function of VBA Excel.

Syntax of the FIND function of VBA

The syntax of the FIND function of VBA is stated as follows:

The “expression” is the range object, which precedes the FIND function in a VBA code. The search range can be one or more rows, columns or the entire worksheet. The FIND function of VBA excel accepts the following arguments:

Only the argument “what” is required. The rest of the arguments are optional.

The Excel VBA Find returns either of the following outcomes:

  • If a match is found, the function returns the first cell where the value is located.If a match is not found, the function returns nothing. This is because the object of the function is set to nothing.

Alternatively, in case a match is not found, a customized message specified in the MsgBox functionMsgBox FunctionVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more can be returned (refer to the code of the succeeding examples).

Note 1: The search begins after the cell specified in the “after” argument and continues till the last cell of the search range. If the value is not found till this last cell, the search begins again from the first cell of the search range till the cell specified in the “after” argument.

So, the cell specified in the “after” argument is searched at the end of the search process. For more details on the usage of the “after” argument, refer to example #2 of this article.

How to use the FIND Function of VBA Excel?

Let us consider some examples to understand the working of the Excel VBA Find.

Example #1–Return the Cell Containing the First Instance of the Search Value

The following image shows some names in column A. We want to perform the following tasks in Excel VBA:

  • Write a VBA code to search the first instance of the name “Aran” in column A.Ensure that cell A2 is selected on running the code.

Use the FIND function of VBA.

The steps to search the given name (first instance) by using the FIND function of VBA are listed as follows:

Step 1: Make sure that the Developer tabDeveloper Tab In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more is enabled in Excel. This tab will help write VBA codes. Once enabled, it will appear on the Excel ribbon, as shown in the following image.

Note: For steps related to enabling the Developer tab, click the given hyperlink.

Step 2: From the Developer tab, click “visual basic.” Next, double-click “sheet1” and a blank pane will appear on the right side.

Write the following VBA code in this pane. This is shown in the succeeding image.

Explanation of the code: The given code is explained as follows:

  • “Sample” is the function name given to “sub.”“FindS” contains the InputBox message.“Rng” is the variable defined for the range. In this example, the range is the entire column A (A:A).

Once the code is run, the InputBox message will appear the way it is shown in the following image. However, before running the code, let us complete it first by executing the subsequent steps.

Note: The InputBox function of VBA displays a dialog box in which the user is required to enter an input.

Step 3: Define the FIND function within the code. Notice that in the “what” argument, we have entered “findstring.” Instead, in place of “findstring,” one can enter whatever one needs to search within the defined range.

So, since “Aran” needs to be searched in column A, it can be entered in place of “findstring.” But, ensure that this name is entered within double quotes. In this way, one can specify the “what” argument of the FIND function.

Step 4: Close the function by entering the arguments (“end if,” “end with,” and “end sub”) shown in the following image.

Step 5: Save the file containing the VBA code as a macro-enabled workbook (.xlsm extension). Next, from the “run” tab, select “run sub/user form (F5).” This command runs the code.

On running the code, the succeeding dialog box appears. It shows the InputBox message that we had entered in step 2. In this box, enter the name “Aran” (with or without double quotes) and click “Ok.” Excel VBA will select cell A2, which contains the name “Aran.”

Had “sheet1” not contained the name “Aran,” Excel VBA would have returned the message “nothing found.” This message is specified in the MsgBox function of the code.

Example #2–Return the Cell Containing the Second Instance of the Search Value

The following image contains some names in the range A1:A10. Notice that the name “Aran” appears twice in column A. Write a VBA code to search and select the second instance of the name “Aran” in column A (i.e., cell A6).

Use the FIND function of VBA Excel.

The steps to search the second instance of the given name by using the FIND function of VBA are listed as follows:

Step 1: Access the Developer tab from the Excel ribbon. Click “visual basic” displayed on the left side of this tab.

From “Microsoft Excel objects,” select “Sheet2.” This is because “Sheet2” of Excel contains the dataset shown in the question of the example.

Note: For more details on customizing the ribbonCustomizing Ribbon In ExcelRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.read more, click this hyperlink.

Step 2: Keep “sheet2” selected and from the “insert” tab, choose “module.” A blank pane appears on the right side, as shown in the following image.

Step 3: Begin to write the code in the blank pane. Define the function as “sub sample2()” and press the “Enter” key. This is the first part of the code, which is written without the double quotation marks, as shown in the following image.

Step 4: Define the variables of the code. This time “rng1” is the variable defined for range. The same is shown in the following image.

Step 5: Define the InputBox function for the “findS” variable. The InputBox message stays the way it was written in step 2 of the preceding example.

Step 6: Enter the name of the worksheet in which the FIND function needs to conduct a search. Specify the range to be searched as well. So, we enter “sheet2” and range “A:A” within double quotation marks. This is shown in the following image.

Step 7: Define the FIND function. At present, the “what” argument contains “findstring.” Since the name “Aran” is to be searched in column A, enter this name in place of “findstring.” Ensure that this name is entered within double quotes.

Further, since the second instance of the name “Aran” needs to be searched, specify the “after” argument. Enter “A2” as the “after” argument. This is because we want the search to begin after cell A2.

The arguments of the VBA FIND function are shown in the following image.

Explanation of the “after” argument: The search begins after cell A2 and continues till the last cell of column A. This is because the search range has been specified as column A (A:A).

So, since the search begins from cell A3, the value “Aran” is found in cell A6 of “Sheet2.” Hence, cell A6 will be selected by the FIND function on running the code.

Had the name “Aran” not been found from cell A3 till the last cell of column A, the search would again begin from cell A1 and end at cell A2 this time. Thus, the cell specified in the “after” argument is searched right at the end of the search process.

Step 8: Close the code by ending the “if” and “with” conditions. Close the “sub” argument by writing “end sub.” The complete code is shown in the succeeding image.

Run the code by selecting “run sub/user form (F5)” from the “run” tab of Excel VBA. The InputBox message appears asking for a value to search. When the name “Aran” is entered in this box, the outcome is the selection of cell A6. This cell contains the second instance of the name “Aran.”

Had a match not been found in the entire “sheet2,” the output would have been “nothing found.” This response is defined by the MsgBox function of the code.

Frequently Asked Questions

This has been a complete guide to the VBA FIND function. Here we learn how to use Excel VBA FIND function with practical examples and a downloadable Excel sheet. You may also look at other articles related to Excel VBA–

The FIND function of VBA searches for a specified value in the range defined by the user. To search, a VBA code is written by entering some or all arguments of the FIND function. One can specify the direction of search, order of search, data to be searched, the format of the search value, and so on.The FIND function returns the cell containing the specified value. If a match is not found, the function does not return anything.Note: For the syntax of the FIND function, refer to the heading “syntax of the FIND function of VBA” of this article.

To find the last occurrence, specify the “after” argument in the VBA code. This argument tells VBA the exact cell after which the search should begin. Note that, at a given time, a single cell reference can be supplied in this argument. It is not possible to list multiple cell references in the “after” argument.The “after” argument is entered following the “what” argument of the FIND function. For instance, if the search range is A1:A25 and cell A20 contains the second last occurrence of the search value, the code is written as follows:With Sheets (“Sheet4”).Range(“A1:A25”)Set Rng = .Find(What:=“textstring”, After:=Range(“A20”))With this code, the search for the value “textstring” begins after cell A20 in “sheet4.” The FIND function searches in the range A21:A25 and returns the last occurrence of the search value. If a match is not found in the range A21:A25, the function searches in the range A1:A20.Note: For details related to the working of the “after” argument, refer to “note 1” of the syntax and the “explanation” in example #2 (after step 7) of this article.

  • Find and Select in ExcelFind And Select In ExcelFind and Select in Excel is a feature available on the Home Tab of Excel that facilitates the user to quickly discover a specific text or value in the given data. The shortcut key to instantly use this feature is Ctrl+F.read moreVBA Find NextVBA Find NextVBA Find Next" means to continue searching for the next value from the found cell until we return to the original cell where we began the search. This is the advanced version of the “Find” method, which searches the specified value in the specified range only once.read moreVBA IsDateVBA IsDateThe IsDate function in VBA determines whether or not a given value is a date. The result will be “TRUE” if the supplied value or range reference value is a date value. If not, then the result is “FALSE.“read more