Open In App

VBA Find Function in Excel

Last Updated: 08 Dec, 2023

A

Ayush Dey
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

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

Parameter

Required

Description
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:

Sample-data

 

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.

Finding-cell-number-for-niladri

 

1. Using After 

In this example, we are instructing the compiler to search for “Utsav” after cell A3.

Using-after-to-find-cell-address

 

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,

Data-sample

 

Code with three different parameters of LookIn.

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,

Data-sample

 

Code to search the word “Tube” in the whole part of the cell.

Searching-tube-in-cell

 

Code to search the word “Tube” in some part of the cell.

Code-to-search-word-in-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.

Data-sample

 

Code to search “Ayush” row-wise and column-wise.

Searching-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:

Data-sample

 

To Search “Ayush” using xlNext the code will return A4 and by using xlPrevious it will return A6.

Searching-ayush-name

 

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:

Data-sample

 

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.

Searching-ayush-name

 

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:

Data-sample

 

The following code is to search for A1 and A2,

Searching-for-A1-and-A2

FAQs on VBA Find Function in Excel

What is the Find Function of VBA Excel?

VBA Find Function is a feature in Excel’s Visual Basic that helps the user to search for the range of value that are specified by the user in the Parameters in large datasets and automating tasks.

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’.



Article Tags :

A

Ayush Dey
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

R

RISHAB NIGAM

Function and Sub in Excel VBA

article_img
In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-procedure generally does not return a result whereas functions tend to return a result. Hence if there is a need for having a value post execution of tasks, then place the VBA code under a function or otherwise place the code under a sub-procedure. In Excel, there is the availability of large numbers of VBA functions that could be utilized in the development of new VBA codes. Such functions are referred to as Built-in functions. With the increase in the size of a VBA program, both Functions and Sub-procedures play a crucial role in the management and performance of VBA code. Functions in VBA A function in VBA can be defined as a procedure that executes a piece of code or instructions and post-execution, it returns the value of the tasks performed. A function is hence invoked using a variable. Functions are directly called in the spreadsheets by using excel based formulas. An excel VBA functi
Read More

N

Nikhil S Kalburgi

How to Debug a User Defined Function in Excel VBA

article_img
Debugging a User Defined Function (UDF) in Excel VBA can feel like a detective mission where you search for clues to fix issues in your code. When a custom function doesn’t deliver the expected results, it can disrupt your workflow and lead to frustration. Fortunately, debugging tools in VBA make it possible to identify exactly what's going wrong and why. In this article you will learn the effective techniques to debug your UDFs, helping you find errors, understand how your code runs, and ensure your functions work seamlessly. Whether you're a beginner or an experienced user, mastering these debugging skills can save you time and turn coding headaches into problem-solving victories.Table of ContentWhat is a User Defined Function in Excel What is Debugging in Excel VBA How to Create a User-Defined Function in Excel VBAHow to Debug a User-Defined Function in Excel VBACommon Debugging Tools in Excel VBAUDF works in debug mode but it doesn't give value into the cell: 9 Best ways to ResolveHow to Debug a Custom Function in ExcelWhat is a User Defined Function in Excel A User Defined Function (UDF) in Excel VBA is a custom function created by users to perform specific tasks or calculatio
Read More

A

Asha Senthilkumar

How to Create a User Defined Function in Excel VBA

article_img
A function is a collection of code.  As a developer, we very often need a custom function (User-defined function) in our projects. These functions can be used as normal functions in Excel. These are helpful when the existing functions are not enough. In such cases, the user can create his own custom user-defined function to fulfil the need.  What are User Defined FunctionsUser Defined Function(UDF) is the custom function that is created by the user to perform a specific task in VBA(Virtual basic application) which is a programming language in Excel. Function Vs. Subroutine in VBA In VBA, a 'Subroutine' lets you run a group of commands, while a 'Function' provides a result. For example, consider a list of numbers, some positive and some negative. With a subroutine, you can scan each cell and mark negative ones by changing their colour. The subroutine modifies the cell properties. On the other hand, a custom function can be used in a separate column. It returns TRUE for negative values and FALSE for positive ones. Functions can't change cell properties directly but can be used with conditional formatting for the same effect. When you create a User Defined Function (UDF) in VBA, you c
Read More

G

Gautam goel

InputBox Function in Excel VBA

article_img
Input Box is one of the most frequently used functions in VBA Macro. The dialogue box that appears asks for input from the user and returns a constant/code/number/text. For example, if you want to create a log-in form in VBA, you will require an input box function. Let's learn how to create an input box in VBA. InputBox FunctionInput Box is a dialogue box that helps users to take value, and do later computations according to the entered value. The input box is similar to the message box, but the message box is used to display the data while the input box is used to enter the data. By default, a message box shows only the OK button in its dialogue box but the input box shows both the OK and Cancel buttons in its dialogue box. You can choose the return type of the data to be entered. The automated data analysis includes entering and displaying the data, which can be achieved if we are using the input box and message box simultaneously. Syntax: InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, context]) Temporary and Permanent Arguments in Input BoxArguments of the Input box, help customize your input box by changing the title, fixing the position, adding help buttons, etc
Read More

A

Asha Senthilkumar

How to Find the Last Used Row and Column in Excel VBA?

article_img
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Sample Data Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address VBA Code: Declaring Variables: VariableData TypeCommentsLastRowLongFind and store last used rowLastColLongstore last used columnLastCellStringstore last used cell address'Variable Declaration Dim LastRow As Long, LastCol As Long, LastCell As String Use SpecialCells function to find last used row/column/cell 'Find Last Used Row LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Find Last Used Column LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column 'Find Last Used Cell LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10). Show the final output in an Excel Message box. 'Display the last used row/column/cell MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column :
Read More

R

Rishabh Chakraborty

How to Use Select Case Statement in Excel VBA?

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to discuss how to use Select Case Statement in Excel VBA. Select Case Statement of Excel VBA The select case in VBA is like a detective that investigates an expression by checking it against different scenarios listed as Case Statements, each with its own conditions. When a scenario matches the expression, it's like solving a piece of the puzzle, and the corresponding code linked to that scenario is activated. Importantly, once a match is found, the detective stops investigating and moves on to executing the discovered code. Yet, if none of the scenarios match, it's like the detective hitting a dead end. In this situation, the code associated with the Case Else statement comes into play, offering a default solution or outcome. Syntax of the Select Case Statement of Excel VBAThe VBA Select Case Statement shares similarities with the Switch Case construct found in programming languages such as Java, C#, and PHP. In Excel VBA, this statement helps
Read More

R

Rishabh Chakraborty

Excel VBA Concatenation Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Concatenation means to join two or more data into a single data. There are various ways we can perform concatenation in Excel using built-in functions, operators, etc. Some helpful links to get more insights about concatenate and using VBA in Excel : Record Macros in ExcelCONCATENATE in ExcelHow to Create a Macro in Excel? In this article, we are going to see about concatenate operators and how to use VBA to concatenate strings as well as numbers. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs at the top of the Excel window.Now select Customize the Ribbon from the pop-down menu.In the Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now, we need to open the Visual Basic Editor. There are two ways : Go to Developer and directly click on the
Read More

R

Rishabh Chakraborty

Excel VBA Comparison Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Some helpful links to get more insights about Macros, VBA in Excel : 1. Record Macros in Excel. 2. How to Create a Macro in Excel? In this article, we are going to discuss various comparison operators in Excel VBA. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs in the top of the Excel window.Now select Customize the Ribbon from the pop-down menu. The Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now click on the Visual Basic option in the Developer tab and make a new module to write the program.Developer -> Visual Basic -> Tools -> Macros Now create a Macro and give any suitable name. This will open the Editor window where can write the code. Comparison Operators in Excel:S.No.OperatorsDefinition 1<> Not equal operator is
Read More

A

Akshit

How to Use for Each Loop in Excel VBA?

A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement Required (Must be mentioned). Variable is used to iterate through the elements of the collection or array. For collections, the element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, the element can only be a Variant variable.groupRequired(Must be mentioned). Name of an object collection or array (except an array of user-defined types).statementOptional (May or may not be mentioned). One or more statements are executed on each item in the group. There are 4 basic steps to writing a For Each Next Loop in VBA: Declare a variable.Write the For Each Line with the variable and collection references.Add line(s) of code to repeat for each item in the collection.Write the Next line to terminate the loop. The For…Each block is entered if there is at least one element in the group. Upon entering the loop,
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox