Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

**VBA Find (Table of Contents)**

## Excel VBA Find

Before we move to VBA and start using find function in macros we need to learn first what is a find function in excel. 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.

When we click on this find and select we get two options;

One is simple to find,

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

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,

### Introduction to VBA Find Excel Function

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 of VBA Find Excel Function:

4.6 (247 ratings)

Expression.Find(What, lookin,….)

If the value we are looking is found with the excel function 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 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 VBA Find Excel function which are optional. The only mandatory field required in a find function is What which is a value we are trying to search.

Basically, VBA find Excel has one required argument which is What which 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 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 whole worksheet.

**Examples of VBA Find Function**

We will learn how to use a VBA Find Excel function with few examples.

#### Excel VBA Find Example #1

Suppose our data has the following values

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

- To write a VBA code it is necessary to have enabled the developer tab in order to be able to write VBA Codes.

- We start writing our code by writing the following code as shown below,

DimFindS As String

Dim Rng As Range

FindS = InputBox (“Enter the value you want to search”)

With Sheets (“Sheet1”) .Range (“A:A”)

- The sample is the function name given to 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,

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

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

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

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

#### Excel VBA Find 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,

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 cell after which reference we want to search the data.

Let us write the code for the above data.

- Always remember to enable developer tab from options and then from customizing ribbons to be able to write the code in VBA.
- In VBA we get Microsoft excel objects which is a module where we write the codes.

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

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

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

- Define what does the Find variable should have,

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

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

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

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

### How to Use VBA Find Function?

The syntax for VBA Find Excel function is as below;

Expression.Find(What, lookin,….)

As defined above expressions in macros are ranges defined. What is a keyword for what we want to search a specific value. There are other constraints in VBA Find function such as lookin which are optional.

### Things to Remember

- First things first we need to enable the developer tab in order to use VBA.
- What is the specific value we need to find?
- 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 codes. You may also have a look at other articles related to Excel VBA –

- Find and Select in Excel
- How to Assign Macro Code to Shapes?
- Excel VBA UBOUND
- Excel VBA InputBox
- What is VBA Range & VBA Cell?
- VBA Excel Range
- VBA Excel MsgBox
- Excel VBA Course
- VBA VLookup
- VBA Arrays

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