Excel VBA: Find text

Written by stephen byron cooper Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
Excel VBA: Find text
You can insert Visual Basic code in Excel spreadsheets. (Courtesy of Microsoft)

Excel has a programmable capability enabling users to created macros with Visual Basic. The Visual Basic implementation in Excel includes a method called Find. This enables you to search through a range of cells or a worksheet for a specific piece of text.

Visual Basic

Visual Basic is a proprietary programming language created and owned by Microsoft. The corporation developed a version of Visual Basic to integrate into its Office Productivity suite and this is called Visual Basic for Applications --abbreviated to VBA. You can access the VBA editor in Excel by pressing the “Alt” and “F11” keys at the same time. You can write VBA applications that only apply to one worksheet or to the whole workbook.


Objects in Excel have properties and methods associated with them. You can access the methods by putting a dot (“.”) after the name of the object and then the method name. A method is a type of function. A property is something that describes the object. For example “Sheets” is an object identifying a collection of charts and worksheets. As every workbook contains more than one sheet, you have to identify which instance of the worksheet you are referring to. Hence, you would write Sheets(“Sheet 1”).Name to get the name of the “Sheet 1” spreadsheet. The methods of the objects are expressed in the same way. For example, to execute the “Select” method of Sheets you would write: Sheets(“Sheet 1”).Select.


One of the methods available to objects in VBA is called Find. It is invoked in exactly the same way as the “Select” example. However it only works on an object of type Range. The range is a group of cells in a worksheet. The method requires a list of parameters to be populated when you call it. Only one of these parameters is compulsory, so it should come first in the parameter list. This is the parameter “What.” Because it is mandatory you don’t have to name it, only pass a value. However, for clarity it is always best to write the parameter name as well as the value. The other parameters for Find are: After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte and SearchFormat.


Find always searches on through a range of cells from a stated position. If no “After” parameter is given, it will start in the first cell in the given range. However it will not search that cell, but only all cells after its start point. Fortunately, if the method gets to the end of the range without finding the first instance of the searched for text, it loops around back to the beginning of the range. This then will check the first cell in the range.


The following example of Find will search through the given range of cells to find the first example of a cell containing “z” Dim ResCell As Range Dim EndCell As Range

With Range("B1:B12") Set EndCell = .Cells(.Cells.Count) End With

Set ResCell = Range("B1:B12").Find(What:="z", After:=EndCell)

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.