VBA FIND

Excel VBA Find

When we use Find in a normal worksheet we press keyboard shortcut CTRL + F and type the data we need to find and if not desired value we go to the next match, if there is a lot of such matches it is a tedious task but when we use FIND in VBA it does the tasks for us and give us the exact match and it takes three arguments, one is what to find, where to find and where to look at.

Before we move to VBA and start using the find function in macros, we need to learn first what is a find function in excelFind Function In ExcelFind function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer.read more. In normal excel, in the Home tab under the editing group, we can find a find function which is used to find a string or a value in a cell range or whole worksheet.

VBA Find

When we click on this, we get two options;

One is simple to find,

VBA Find 1

We can see it also has a mode of options that opens up another feature.

VBA Find 2

It does the find algorithm with four constraints, Find What, Within, Search, and look in.

The second option in excel is to Find and replace which is used when we find a string but what to replace it with any other value,

VBA Find 3

Find Function Syntax

We have learned above what is Find in basic excel. In VBA, we write codes manually, but the features are the same as normal excel. First, let us look at the syntax.

Expression.Find(What, lookin,….)

If the value we are looking is found with the excel functionExcel FunctionExcel functions help the users to save time and maintain extensive worksheets. There are 100+ excel functions categorized as financial, logical, text, date and time, Lookup & Reference, Math, Statistical and Information functions.read more, it returns the cell where the value is, and if the value is not found, then the object of the function is set to nothing.

Expressions in macros are ranges defined, such as range 1 or range 2. What is a keyword for what we want to search for a specific value? Lookin is a keyword for what we are trying to search, is it a comment or a formula or a string. Similarly, there are other constraints in the Find function, which are optional. The only mandatory field required is what is a value we are trying to search.

 

Basically, VBA finds Excel has one required argument, which is What value we want to search. The rest of the constraints are optional, and there are many constraints in find function. Find function is similar to what a find function is in excel.

The parameter for the find function is the range of cells, like in which range we want to find a value. It can be a few columns or few cells or a whole worksheet.

Examples

You can download this VBA FIND Function Excel Template here – VBA FIND Function Excel Template

Example #1

Suppose our data has the following values

VBA Find Example 1

We will try to find “Aran” in the same data.

VBA Find Example 1-1

  • We start writing our code by writing the following code as shown below,
Sub Sample ()
DimFindS As String
Dim Rng As Range
FindS = InputBox (“Enter the value you want to search”)
With Sheets (“Sheet1”) .Range (“A:A”)

VBA Find Example 1-2

  • The sample is the function name given to the sub.
  • Find is the string we want the user we want to enter to search.
  • Rng is the variable we took for the range.
  • Now we ask the user to enter the value which looks like the screenshot below,

VBA Find Example 1-3

  • Now we will define our find function in the module.

VBA Find Example 1-4

  • The function finds the value entered by the user in the given range.
  • Now we close the function by the following arguments.

VBA Find Example 1-5

  • Now, if we run our code first, it asks for a prompt by the user for a value.

VBA Find Example 1-3

  • Once the code is completed, it returns the cell to where the data was found.

Example #2

In the above example, there were four unique names, but what if there were more than one names in the data, such as consider the below data,

Example 2

We can see that the name Aran is repeated twice in the above data. If excel has to find the name Aran, it will find it in cell A2 and stop, but there is another value similar to that of A2 in cell A6. How to fetch that value? Here comes the syntax of Find(What, After) in help.

After defines the cell, after which reference, we want to search the data.

Let us write the code for the above data.

Example 2-2

  • Previously we were working on sheet 1; now, we are working on sheet 2, so select sheet 2 for another module, and a blank page appears.

Example 2-3

  • Now start writing the code by defining the function first as SUB Sample2() and press enter.

Example 2-4

  • Now we have defined our function; we will start getting into the main part, which is defining our variables.

Example 2-5

  • Define what does the Find variable should have,

Example 2-6

  • Select the sheets which we are working on, which is sheet 2 in this example,

Example 2-7

  • Now we will find the text whatever the user enters after the A2 cell, so we define our find function as below,

Example 2-8

  • Now we close the code by ending the with and if conditions.

Example 2-9

What the above code does is search the string after the cell A2 and return the cell wherever it is found.

Things to Remember

  1. First things first, we need to enable the developer tab in order to use VBA.
  2. What is the specific value we need to find?
  3. If the value is not found, the object of the function is set to nothing.

Recommended Articles

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

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *