VBA Find

What Is FIND Function In VBA Excel?

The VBA FIND function is used to find a specific value in a specified range. Also, the function can return either an exact or a partial match.

The Find function in VBA helps us to find the desired value in the given range of cells with various permutations and combinations. VBA Find function will return the cell containing the given value in the specified range of cells; if no matches are found, then, it will not return anything. The find could be exact as well as partial. For example, look at the simple data in Excel.

Assume, we need to find the value June in the above data range; then, we must open the Find and Replace window by pressing the Ctrl + F shortcut keys.

Next, in the Find What: box, enter the value that we need to search, i.e., June.

Then, click on Find Next, and it will locate the given value cell.

In cell B2, we have the value June. The same kind of activity can be automated using the FIND function in VBA.

Syntax Of The FIND Function Of VBA

FIND is a method in VBA available with a Range or Cells object. The syntax of the FIND function is as follows:

Range(“Desired Range”).Find(What, [After], [Lookin], [Lookat], [SearchOrder], SearchDirection, [MatchCase], [MatchByte], [SearchFormat] )

Range(“Desired Range”) – To access the FIND method in VBA, first, we need to provide the range of cells from where we are searching for.

Find Method Syntax Explanation:

What – Here, we need to enter the value that we are searching for. This is similar to the Find What option in the Excel worksheet.

After – In this argument, we can provide the cell address from which the search should start. If we ignore this, the search will begin from the upper left corner of the data range.

Lookin – In this argument, we can mention where the search should happen. Options include:

xlComments (to search in comments)

xlFormulas (to search in formulas)

xlValues (to search in values).

Lookat – This argument is used to match the value as exact or partial and search the value of the entire cell or part of the cell. We have two options.

xlWhole is used for exact match

xlPart is used for the partial match

The default value will be xlPart.

SearchOrder – This argument is used for the order of the search, whether it is by rows or columns. Options include:

xlRows for search by rows

xlColumns for search by columns

The default will be xlRows.

SearchDirection – This argument is used for the direction of the search. We can use xlPrevious for upward search and xlNext for downward search. xlNext is the default value.

MatchCase – This is used for case-sensitive search. To search based on case-sensitive, we need to set this argument property to TRUE or FALSE. The default value is FALSE.

MatchByte – This must be specified as TRUE if double-byte characters are to be matched with double-byte characters and FALSE if double-byte characters are to be matched with their single-byte equivalents.

SearchFormat – Using this, we can mention the formatting technique to be followed when the search begins. We can mention the argument as TRUE or else FALSE. The default value will be FALSE.

Note: Only the What argument is mandatory.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use The FIND Function Of VBA Excel?

Let us show you a simple example using VBA FIND function in Excel for better understanding. For instance, look at the following data in Excel.

We will try to find the value June using the VBA FIND function.

Step 1 – First, start the sub procedure by naming the macro.

Step 2 – Next, define a variable and assign it the VBA variable type as String.

Step 3 – For this variable, we will assign the Find method result. Hence, enter the variable name and then enter the equal sign.

Step 4 – To access the VBA Find method, first, we need to provide the range of cells where it has to search. Hence, provide the range as A1:D3 by using the Range object.

Step 5 – After providing the range, enter the dot and then, choose the Find method from the IntelliSense list.

Step 6 – Then, open the parenthesis, and we will see the first argument as What.

Step 7 – Next, for the argument What, we need to provide the value that we are searching for i.e., June.

Step 8 – Now, close the parenthesis. We need to find the cell address of the given value. So, to get the cell address, use the Cells.Address property.

Step 9 – Then, we should not enter any other arguments. We need to show the variable value in a message box. Hence, enter the message box function followed by the variable name.

Next, let’s run this code, and we will get the given value’s cell address in the message box.

The value we are searching for is in cell B2.

Examples

Example #1 – Using After With Find

We have seen the basic example of applying the Find method. Now let us explore other arguments of the VBA Find function.

For instance, look at the following data.

Assume, we need to find the city name ‘Pune,’ then we can use the following code.

Sub Find_Example_After()
Dim CityName As String
CityName = Range(“A1:B7”).Find(“Pune”).Cells.Address
MsgBox CityName
End Sub

This will return the cell address as A2 because it has found the first occurrence of the city ‘Pune’ in that cell.

If we want to search for the value after cell A2, then, we need to make use of the After argument of the Find method.

Sub Find_Example_After()
Dim CityName As String
CityName = Range(“A1:B7”).Find(“Pune”, Range(“A2”)).Cells.Address
MsgBox CityName
End Sub

For the after argument of the VBA Find method, we have given the cell address as A2; so, it means the find will start after cell A2 only.

Now it will not show the address of the cell as A2 but A4.

Since we have provided the find option to look after cell A2, it has ignored the value ‘Pune’ in cell A2 and searched after that cell, and returned the next matching cell A4.

Example #2 – Using LookIn With Find

Now, let’s learn how to use the Lookin argument of the Find function. The Lookin option is used to find the desired value in comments, formulas, and values.

For example, we have inserted a few comments in the same data.

Cells A1, B4, and B7 contain the comments as India, Australia, and India respectively.

Assume, we need to find the value Australia in comments. Then, we need to set the Lookin argument of the VBA Find method to xlComments Threaded.

The following code is the sample for you.

Sub Find_Example_Lookin()
Dim CityName As String
CityName = Range(“A1:B7″).Find(What:=”Australia”, LookIn:=xlCommentsThreaded).Cells.Addres
MsgBox CityName
End Sub

This will return the cell address as B4 because in cell B4, we have a comment ‘Australia.’

Example #3 – Using LookAt With Find

Look at argument is used to look for the exact match or partial match. For example, look at the following data.

We have two values in cells A1 and A2. Now, let us see how to use the Look at argument in the VBA Find method.

In the Look at argument, we can use either xlPart or xlWhole. Now, consider the following code.

Sub Find_Example_Lookat()
Dim CityName As String
CityName = Range(“A1:A2″).Find(What:=”Sydney”, Lookat:=xlWhole).Cells.Address
MsgBox CityName
End Sub

This will locate cell A2 because the whole value is there in cell A2.

Similarly, look at the following code.

Sub Find_Example_Lookat()
Dim CityName As String
CityName = Range(“A1:A2″).Find(What:=”Sydney”, Lookat:=xlPart).Cells.Address    
MsgBox CityName
End Sub

This will locate cell A1 because we are finding only the partial value.

Example #4 – Using SearchOrder With Find

The search order argument helps us to find the value either through rows or columns. By default, the VBA Find method looks for the given value in rows. For example, look at the following data.

The following code will help us to find the cell address of the city name ‘Mumbai.’

Sub Find_Example_SearchOrder()
Dim CityName As String
CityName = Range(“A1:D3”).Find(“Mumbai”).Cells.Address
MsgBox CityName
End Sub

This code should return the cell address B2 since, by default, it searches values in rows.

However, let us set the look at argument to xlColumns and see what happens.

Sub Find_Example_SearchOrder()
Dim CityName As String
CityName = Range(“A1:B7″).Find(What:=”Mumbai”, SearchOrder:=xlColumns).Cells.Address
MsgBox CityName
End Sub

We have set the SearchOrder to xlColumns and now it will look for the given value in the first column and then move to the second column.

We are getting the cell address as A7 instead of B2; this is because, find happens on a column basis, not on a row basis.

Example #5 – Using SearchDirection With Find

The search direction will search for the values either upwards or downwards.

The above code will search the values upwards starting from cell A6, because we have set the search direction to xlPrevious.

Sub Find_Example_SearchDirection()
Dim CityName As String
CityName = Range(“A1:B7″).Find(What:=”Mumbai”, After:=Range(“A6”), SearchDirection:=xlPrevious).Cells.Address
MsgBox CityName
End Sub

Example #6 – Using MatchCase With Find

MatchCase is used for case-sensitive search of the data. For example, look at the following data.

In yellow-colored cells, we have small case values.

Therefore, to search for case-sensitive values, we need to set the MatchCase argument to TRUE.

Sub Find_Example_SearchOrder()
Dim CityName As String
CityName = Range(“A1:B8″).Find(What:=”pune”, MatchCase:=True).Cells.Address
MsgBox CityName
End Sub

We are searching for the value ‘Pune’ with a small case and set the match case argument to TRUE.

This will return the cell address as A8 even though we have a value in cell A2 because of the case-sensitive search.

Example #7 – Using MatchByte With Find

The match byte argument is used for languages with a double-byte character set. These languages are Chinese, Japanese, and Korean.

If you are not using these languages, this parameter is not relevant.

We can set this argument value to either TRUE or FALSE.

Example #8 – Using The Wildcard

We can also use wildcards with the find method. For example, look at the following code.

Sub Find_Example_WildCard()
Dim CityName As String
CityName = Range(“A1:B8″).Find(What:=”P*”).Cells.Address
MsgBox CityName
End Sub

In the above code, we have given the finding value as ‘P*’ i.e., any value starting with P will be retrieved with this wildcard asterisk (*).

Example #9 – Finding The Last Cell Containing Data

It is important to find the last used cell dynamically to carry out operations in Excel.

The following code will help us to find the last used cell in the worksheet.

Sub Find_Example_LastCell()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LR
End Sub

This will return the last used row of the active worksheet.

Example #10 – Finding Cells With Patterns

If you want to find the cells with any formatting, then we need to set the SearchFormat to TRUE. For example, look at the following data.

We have 3 cells formatted with colors. The following code will help us to find the format of applied cells.

Sub Find_Example_WildCard()
Dim CityName As String
CityName = Range(“A1:B8″).Find(What:=”P*”).Cells.Address
MsgBox CityName
End Sub

The above code will search for the first formatting cell i.e., cell A2.

Important Things to Note

• VBA FIND is a method used to find a value and can be accessed only when we provide a reference to the range of cells.
• The What parameter is the only mandatory argument, the rest are optional.
• In VBA, Find will always happen based on rows, top to bottom.
• We get a run time error if the given value is not found within the specified range.
• Wildcard in excel (*) is used to match any number of characters.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA Find, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Find. Here we explain how to use find function in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA Object Required
• VBA AutoFilter
• VBA Arrays