Fill Methods

The Excel object model has four Fill methods: FillDown, FillUp, FillLeft, and FillRight. As expected, these methods work similarly, so we will describe only FillDown.

The FillDown method fills down from the top cell or cells in the specified range to the bottom of the range. The contents, formulas, and formatting of the cell or cells in the top row of a range are copied into the rest of the rows in the range. The syntax is:

RangeObject.FillDown For instance, the code:

Range("B3:D5").FillDown will duplicate the values of cells B3 through B5 in cells C3 through C5 and D3 through D5.

19.3.25 Find Method

The Find method returns the first cell in a given range that satisfies a criterion. Note that the Find method returns Nothing if no match is found. In any case, it does not affect the selection or the active cell.

The syntax of the Find method is:

RangeObject.Find(What, After, LookIn, LookAt,

SearchOrder, SearchDirection, MatchCase, MatchByte)

Note that all of the parameters except What are optional.

The What parameter is the data to search for and can be a string or any other valid Excel data type (number, date, etc.).

The After parameter is the cell after which the search should begin. (This would be the active cell when doing a search from the user interface.) Thus, the cell referred to by After is the last cell searched. If the After argument is omitted, the search starts after the cell in the upper-left corner of the range.

The LookIn parameter is one of the following constants:

Enum XlFindLookIn xlValues = -4163 xlComments = -4144 xlFormulas = -4123 End Enum

The LookAt parameter is one of the following constants that determines whether the What value must match the cell's entire contents or just any part of the cell's contents:

Enum XlLookAt xlWhole = 1 xlPart = 2 End Enum

The SearchOrder parameter is one of the following XlSearchOrder constants:

Enum XlSearchOrder xlByRows = 1 xlByColumns = 2 End Enum

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

If c.Value Like "A*" Then c.Font.Bold = True End If Next

0 0

Post a comment