Microsoft Office Tutorials and References
In Depth Information
19.3.26 FindNext and FindPrevious Methods
The SearchDirection parameter is one of the following XlSearchDirection constants:
Enum XlSearchDirection
xlNext = 1 ' Default
xlPrevious = 2
End Enum
The MatchCase parameter should be set to True to do a case-sensitive search; otherwise, the
search will be case-insensitive. (The MatchByte parameter is used only in the Far East version of
Microsoft Excel. See the help documentation for details.)
There are several things to note about the Find method:
The values of the LookIn , LookAt , SearchOrder , MatchCase , and MatchByte
parameters (but not the SearchDirection parameter) are saved each time the Find
method is invoked and are then reused for the next call to this method. Note also that
setting these arguments changes the corresponding settings in Excel's Find dialog box,
and, conversely, changing the settings in the Find dialog box changes the values of these
parameters. This implies that we cannot rely on the values of these parameters, since the
user may have changed them through the Find dialog box. Hence, it is important to
specify each of these arguments for each call to the Find method.
The FindNext and FindPrevious methods (described in the next section) can be used to
repeat a search.
When a search reaches the end of the specified search range, it wraps around to the
beginning of the range. If you do not want this behavior, consider using a different range.
To find cells that match more complicated search criteria, such as those involving
wildcard matches, we must use a more manual approach, such as cycling through the cells
in the range with a For Each loop and using the Like operator. For instance, the
following code searches for all cells in the range A1:C5 whose contents begin with an
"A" and sets the font for these cells to bold (note the use of the evaluation operator to
denote the range A1:C5):
Dim c As Range
For Each c In [A1:C5]
If c.Value Like "A*" Then
c.Font.Bold = True
End If
Next
19.3.26 FindNext and FindPrevious Methods
The FindNext method continues a search that was started with the Find method, returning the next
cell that matches the criteria. The syntax is:
RangeObject .FindNext( After )
The After parameter must be specified or the search will begin at the upper-left corner of the
range. Thus, FindNext is the same as Find, except that it uses all of the parameters (except After )
that were set by the previous use of the Find method.
To continue the search from the last cell found, use that cell as the After argument. For instance,
the following code searches for all cells in the top row that contain the value 0 and removes the
value:
Dim c As Range
Search JabSto ::




Custom Search