The Find function is often useful in VBA. You can use the Find tool to know a specific value or format in a group of cells. Using Find is functionally equivalent to using Excel’s Find Dialog. Ctrl + F is a keyboard shortcut that searches the entire sheet and workbook for the specified word or value.
Syntax
Expression.Find( What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat )
The syntax for using VBA’s Find function is outlined below. Everything is optional except for the first parameter.
What: What we are searching for. We must specify the nature of the data we seek.
After: What cell do we want to look for after
Look In: The place we should start looking for what we need. For instance, the parameters are values, Formulas, and Explanatory Notes. xlFormulas, xlValues, and xlComments.
Look at: Whether we want to find the whole thing or just a part. The xlWhole and xlPart values are the parameters.
SearchOrder: The order of the rows or columns we want to find. xlByRows or xlByColumns are the two types of parameters.
SearchDirection: Whether we want to look for the next cell or the cell before it. xlByColumn and xlByRows are the parameters.
MatchCase: Whether or not the search is case-sensitive. All parameters are either True or False.
MatchByte: It is only useful for languages with two bytes. Conditions are either True or False and are called parameters.
SearchFormat: If we want to search by format, use the Application. FindFormat function.
See More: Excel VBA Range
Excel Find Dialog
Navigate to the Home tab on the ribbon, and then select Find & Select to open
It will bring up the following dialogue as following.
The Find function in VBA extensively uses most of the parameters in this Dialog.
How to use Find Function
Example: Let’s say we have the contact names and the data from their reports. And here’s how we are looking for Peter:
Step 1: Open Visual Basic and add a new module.
Step 2: The newly added module by double-clicking it.
Step 3: Find function is a portion of the Range property. So, we should start with the range. Here, we have a degree from A2 to A6.
Step 4: After entering the range, you need to enter a dot and the Find operator.
Step 5: The Find property appears in the drop-down menu.
Step 6: Select the bracket, click the Find menu, and click Open.
Step 7: The Find function’s syntax is displayed.
Step 8: We pass the argument, such as What:=, which will help us figure out the parameter.
Step 9: Pick that word, put a dot after it, and then send Select as an argument.
Step 10: Once you have chosen the Select argument, use the Run button to run the code.
Step 11: The code in the Excel worksheet highlights the word “Peter” as shown in the screenshot below.
