The Excel ‘Find’ method, as you may have suspected, helps you find data in a spreadsheet. In theory, it works the same way as using loops, but is far more efficient. We typically use the Find method to search for bits of data within a range, which we can then extract or act on. This method is especially useful in large spreadsheets with a lot of scattered data.
In this tutorial, we will learn about the Find method, how it works, its applications and some examples. For more details on the Find method and other advanced VBA applications, take a look at this online course on using Visual Basic in Excel.
What is the Find Method?
The traditional method of finding data in a worksheet is to use loops. Although effective, this method is extremely time consuming and inefficient, especially in large data sets. The very nature of the loop means Excel has to go through the same data repeatedly to find the required information.
The Find method achieves similar aims far more efficiently. Since it is a specific function designed only to search for data, it skips the looping part entirely. Furthermore, it also gives you a lot of control over what and where to look for data. The end result is a far superior search method that makes it possible to search for data according to many different parameters.
The Find method can be written as follows:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
That looks complicated!
Let’s break it down into smaller chunks:
1. What (required): The only required parameter, What tells the Excel what to actually look for. This can be anything – string, integer, etc.).
2. After (optional): This specifies the cell after which the search is to begin. This must always be a single cell; you can’t use a range here. If the after parameter isn’t specified, the search begins from the top-left corner of the cell range.
Syntax: expression.Find(What:=”x”, After:=ActiveCell)
Here, we’ve used ‘ActiveCell’ as our starting cell, though you can also specify a particular cell.
New to Excel programming? This course will teach you Excel programming for business professionals.
3. LookIn (optional): This tells Excel what type of data to look in, such as xlFormulas.
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas)
4. LookAt (optional): This tells Excel whether to look at the whole set of data, or only a selected part. It can take two values: xlWhole and xlPart
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart)
5. SearchOrder(optional): You have the choice of telling Excel whether to search by rows or by columns, i.e. xlByRows or xlByColumns
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows)
6. SearchDirection(optional): This is used to specify whether Excel should search for the next or the previous matching value. You can use either xlNext (to search for next matches) or xlPrevious (to search for previous matches).
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
7. MatchCase(optional): Self-explanatory; this tells Excel whether it should match case when doing the search or not. The default value is False.
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
8. MatchByte(optional): This is used if you have installed double-type character set (DBCS). Understanding DBCS is beyond the scope of this tutorial. Like MatchCase, this can also have two values: True or False, with default being False.
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MarchByte:=False)
The MatchByte parameter is usually not a part of the Find range if you record a macro using Excel’s built-in Find & Replace function (CTRL + F).
9. SearchFormat(optional): This parameter is used when you want to select cells with a specified property. It is used in conjunction with the FindFormat property. Say, you have a list of cells where one particular cell (or cell range) is in Italics. You could use the FindFormat property and set it to Italics. If you later use the SearchFormat parameter in Find, it will select the Italicized cell.
SearchFormat can have two values: True and False. Default is false.
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MarchByte:=False, SearchFormat:=False)
Find Method Example:
Let’s say we have a spreadsheet where the first column is filled with an arithmetic progression:
1, 4, 7, 10, 13, 16, 19….
The entire column from A1 to A65000 is filled. We want to find a specific value, say, 24652, in this progression.
To do this, we can enter the following formula:
Cells.Find(What:="24652", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
Which immediately finds us our required cell – A8218 upon running the macro:
We entered all the parameters above for illustrative purposes. In your actual formula, you can use just the value to be searched (“What:=”).
Instead of using ‘Cells.’ At the beginning, you can also specify a particular range.
To see how different search options affect the Find method syntax, try recording macros with Excel’s built-in Find function (CTRL + F).
You can also use .FindNext and .FindPrevious to search for next/pervious matching values. Keep in mind that when you use the Find method once, Excel stores all the parameters you entered (‘SearchFormat’, ‘MatchCase’, etc.). Thus, if you set ‘MatchCase’ to true once, it remains true for subsequent searches as well until you explicitly change it to false.
Want to use macros but hate programming? Try this non-coding approach to Excel VBA and macros.
You’ll use the Find method a lot to find and/or replace values data in your VBA programs. Its primary applications are:
- Search for and replace values in Cell Value
- Search for values in Cell Formula
Most importantly, the Find method acts as a far more efficient alternative to using loops to look for data. The performance boost is very significant – a search like the kind outlined above in our example using loops would take anywhere from 20-120 milliseconds. The same with Find takes less than 5 milliseconds.
If you’re in the habit of using loops, you’ll find a worthy (and easier to use) ally in Find. If you’re new to the Find method and VBA programming in general, you can turn to the Excel VBA and macros course with MrExcel.
What are your personal favorite tips for using the Excel VBA macros? Share them with us below!