Page 213 - Excel Progamming Weekend Crash Course

k540629 ch15.qxd 9/2/03 9:34 AM Page 188

188 Saturday Afternoon

The Find Method

The Find method locates data within a range, and although it is a rather complicated
method, it offers a great deal of flexibility. The syntax is:

SomeRange.Find(What, After, LookIn, LookAt, SearchOrder, _
SearchDirection, MatchCase, MatchByte)

What is the data to find and can be any Excel data type. This is the only required
argument of the Find method.
After specifies the single cell in the range after which the search is to start.
Optional; if omitted, the search starts in the top left cell of the range.
LookIn specifies what type of data is to be searched. Possible values are xlFormula
(search cells that contain formulas), xlValues (search cells that contain text or
numeric values), and xlComments (search cell comments) Optional; the default is
xlValues.
LookAt can be either xlPart or xlWhole specifying whether the entire data item or
only part of it is to be matched. The default is xlPart (searching for “Jack” will
match “Jackson.”)
SearchOrder can be either xlByColumns or xlByRows specifying the order in which
the cells in the range are searched. The default is xlByColumns (the search goes
across then down).
SearchDirection is either xlNext or xlPrevious specifying the direction of the
search. The default is xlNext.
MatchCase is True or False specifying whether the search is case-sensitive for text
matches. The default is False.
MatchByte is relevant only when double-byte language support is installed. True
means that double-byte characters match only double-byte characters, while False
means that double-byte characters match the single-byte equivalents. The default
is False.

When you call the Find method, values that are passed for the LookIn,
LookAt, SearchOrder, and MatchByte arguments are saved and will be in
Tip effect the next time you call Find unless you explicitly change them.
The Find method returns a Range object that references the first cell in the range in
which there is a match. If there is no match, the method returns the special value Nothing.
The following program finds the first occurrence of a target in a range. If it is found, the
cell is selected. If it is not found, a dialog box to that effect displays. This program is shown
in Listing 15-1, which also includes a program named TestFind that you run to call the
procedure FindDataInRange. You should change the arguments that are passed to
FindDataInRange as needed.