It is used in conjunction with the FindFormat property. SearchFormat(optional): This parameter is used when you want to select cells with a specified property. 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).ĩ.
Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MarchByte:=False) Like MatchCase, this can also have two values: True or False, with default being False. Understanding DBCS is beyond the scope of this tutorial. MatchByte(optional): This is used if you have installed double-type character set (DBCS). Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)Ĩ. MatchCase(optional): Self-explanatory this tells Excel whether it should match case when doing the search or not. Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)ħ. You can use either xlNext (to search for next matches) or xlPrevious (to search for previous matches).
SearchDirection(optional): This is used to specify whether Excel should search for the next or the previous matching value. Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows)Ħ. SearchOrder(optional): You have the choice of telling Excel whether to search by rows or by columns, i.e. Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart)ĥ. It can take two values: xlWhole and xlPart LookAt (optional): This tells Excel whether to look at the whole set of data, or only a selected part. Syntax: expression.Find(What:=”x”, After:=ActiveCell, LookIn:=xlFormulas)Ĥ. LookIn (optional): This tells Excel what type of data to look in, such as xlFormulas. New to Excel programming? This course will teach you Excel programming for business professionals.ģ. Here, we’ve used ‘ActiveCell’ as our starting cell, though you can also specify a particular cell. Syntax: expression.Find(What:=”x”, After:=ActiveCell) If the after parameter isn’t specified, the search begins from the top-left corner of the cell range. This must always be a single cell you can’t use a range here. After (optional): This specifies the cell after which the search is to begin. This can be anything – string, integer, etc.).Ģ. What (required): The only required parameter, What tells the Excel what to actually look for. The Find method can be written as follows:įind( What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)ġ. The end result is a far superior search method that makes it possible to search for data according to many different parameters. Furthermore, it also gives you a lot of control over what and where to look for data. Since it is a specific function designed only to search for data, it skips the looping part entirely. The Find method achieves similar aims far more efficiently. The very nature of the loop means Excel has to go through the same data repeatedly to find the required information. Although effective, this method is extremely time consuming and inefficient, especially in large data sets. The traditional method of finding data in a worksheet is to use loops. 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. In this tutorial, we will learn about the Find method, how it works, its applications and some examples. This method is especially useful in large spreadsheets with a lot of scattered data. We typically use the Find method to search for bits of data within a range, which we can then extract or act on. In theory, it works the same way as using loops, but is far more efficient.
The Excel ‘Find’ method, as you may have suspected, helps you find data in a spreadsheet.