Arguments needed
Microsoft.com gives you a summary of parameters and arguments for MS Excel functions.
This means that the FIND function can be used on a Range object on the worksheet. It is used in the format:
Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
The answer should be saved as a Range object, so the Set keyword should be used when using the FIND() method.
The only required parameter is what is being looked for and the rest is optional. The optional parameters have default values corresponding to whatever was selected last on a manual search. For example, if the user specified to search within the Excel comments in the last manual search, the FIND() function will then only look at Excel comments if a LookIn value is not specified—which may or may not be how the FIND() function is expected to run. Because of this, it’s recommended to specify the parameters listed below to make sure the function runs in a way that is expected:
- LookIn – decides where the variable is to be found (xlFormulas, xlValues, xlNotes)
- LookAt – full or partial match (xlWhole, or xlPart)
- MatchCase – TRUE to make the search case sensitive. Default value is FALSE
- After – useful when looking for multiple matches since it specifies the cell after which the search should begin
Featured Course
Unlock Excel VBA & Excel Macros
The FIND() method to find one match
In this example, the button One Match should display the corresponding article code from the given data table depending on what company code the user selects.
- Bring up the Visual Basic editor (ALT + F11).
- Create a new module by going to Insert > Module.
- Create a new Subprocedure
Find the company ID match
1. Write the variable to keep the result of the FIND() function. In this example, the variable CompId is used and is written as:
Dim CompId As Range2. Since CompID is a range, the Set keyword is used to put a value in it. You have the option to specify the parameter and equating a value to it using the “:=” symbols, or use the symbol “,” to skip parameters when using the default parameter order.
Set CompId = Range(“A:A”).Find (What:=Range(“B3”).Value, LookIn:=xlValues, LookAt:= xlWhole)- What:=Range(“B3”).Value – the value to be searched
- LookIn:=xlValues – looks at the cell values
- LookAt:=xlWhole – full match
Note: cells.Find can be used if you want to search the entire worksheet or don’t know where the company Id column is.
Activate the watch window
Activating the Watch Window (under View) helps you identify what steps are missing in the code. Highlight the variable to be watched and drag it to the watch window. Run the code (or press F8).
The watch window shows the value of CompId, which is the match of the Company Id selected by the user. To better understand exactly which cell it is pointing to, change the Expression from CompId to CompId.Address. This will then give you $A$8, which corresponds to the address of the match.
Display the equivalent Article Code
Since the corresponding Article Code should be displayed in cell C3, set up the formula to get the Article code which is 4 columns to the right of the company id on the data table using the Offset function:
Offset([RowOffset], [ColumnOffset])This now becomes:
Range(“C3”.Value=CompId.offset(,4).Value
Note: A 0 can be used for the [RowOffset] parameter, or left blank and skipped by the symbol “,”
Address values that are not found on the table
When the selected Company ID is not found on the table, the watch window shows an error in CompId.Address because the CompId.Value is nothing and is invalid for the Range data type.
An IF statement should be added to address such cases. In VBA, the keyword NOT is used often since it is usually easier to specify what something isn’t, than what something is. In this case, the result can either be nothing or specific ranges. When it is nothing, you can alert the user with a message box indicating so. The IF statement then becomes:
If Not CompId is Nothing Then
Range(“C3”).Value=CompId.offset(,4).Value
Else Msgbox “Company not found!”
End If
However, while it displays the message box when the company id is not found, the value in cell C3 retains the result of the previous search. Make sure that the macro deletes the contents of the cell before it runs the rest of the code:
Range(“C3”).ClearContents
Assign the macro to the button
Right click on the button > Assign macro. Select the macro name.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
