Excel VBA FIND Function (& how to handle if value NOT found)

Doing a CTRL + F on Excel to find a partial or exact match in the cell values, formulas or comments gives you a result almost instantly. In fact, it might even be faster to use this instead looping through multiple cells or rows in VBA.  MS Excel’s FIND method automates this process without looping.

Arguments needed

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

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.  

  1. Bring up the Visual Basic editor (ALT + F11).
  2. Create a new module by going to Insert > Module.
  3. 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 Range

2. 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], [CoulmnOffset])

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.

Video

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE