Working With Cells

Excel remembers the values specified for the LookIn, LookAt, SearchOrder, and MatchByte parameters. If you run a search again without these parameter values, Excel uses the settings from the previous Find or Replace method. These values are also modified if you run a Find or Replace from within Excel. To avoid running searches that have unexpected values set, you should set these values each time you run the method.

You can continue a search and find the next match using the FindNext method. In order to use this method you must specify an After parameter indicating the cell from which to start the next search. All other parameter values are used from the previous Find method. You use the FindNext method as illustrated:

Example:

SearchRange.FindNext(After)

Instead of finding the next occurrence of a string, you can find a previous occurrence using the FindPrevious method. This method searches backward from the specified location and finds a previous occurrence of a string. The FindPrevious requires one parameter, Before, which indicates the cell where the search should begin. You use the FindPrevious method as illustrated:

Example:

SearchRange.FindPrevious(Before)

Q Type

SearchOrder:=xlByColumns.

Q Type LookAt:=xlWhole, replacing xlWhole with the constant indicating part of string to match.

ype LookIn:=xlValues, replacing xlValues with the XlFindLookIn constant value indicating the part of cell to search.

Q Type

SearchOrder:=xlByColumns.

_Q Type .Activate to activate the cell.

IjjP] File Edit View Insert Format Tools Data Window Help

Type

question For help

-.Sx

1 □

GiQiU iû?

Iß m

1 Arial

.10 - \b] 7 U

---I'

A4

f,\ Bois

A

I

c

D

E

F

G

H

I

1

Sales Totals

v

Januarv

February

March

April

May

June

"i

000.00

$100,000.00

$187,000.00

$252,700.00

$351,700.00

$406

700.00

4

Boise

—Î"^

n

$377.000.00

$422

300.00

b

Dallas

$98

000.00

$153,000.00

$198,000.00

$253,000

I0

$298,000.00

$396

000.00

6

Denver

$87

mot

$152,700.00

$251,700.00

$329,200

0

$394,900.00

$482

900.00

7

Ft. Worth

$65

700.0t

$163,700.00

$204.700.00

$249,700

0

$347.700.00

$450

700.00

8

Houston

$99

300.0t

$187,000.00

$252,700.00

$351,700

0

$406,700.00

$472

400.00

9

Las Vegas

$88

mot

$187,000.00

$285,000.00

$330,000

ü

$371,000.00

$458

300.00

10

Little Rock

$41

ma

$139,900.00

$192.900.00

$277,400

0

$358.900.00

$457

300.00

11

Los Angeles

$103

mot

$201,000.00

$289,000.00

$330,000

0

$417,000.00

$470

300.00

12

Miami

$96

900.0t

$201,900.00

$256,900.00

$338,400

ü

$426,400.00

$481

400.00

13

Salt Lake

$53

mot

$118,700.00

$196.200.00

$294,200

0

$378.700.00

$419

700.00

14

San Antonio

$81

500.0t

$122,500.00

$221.400.00

$274,400

0

$351.900.00

$450

900.00

1b

Seattle

$77

500.0t

$176,500.00

$258,000.00

$323,700

ü

$376,700.00

$474

700.00

16

St. Louis

$84

500.00"

$172,500.00

$271,500.00

$356,000

0

$454,900.00

$557

900.00

17

18

19

20

21

22

23

24

25

2b

_

H

t n\sheet6

sheets

\sheetl i Sheet2 /, Sheet4 / sheets

H

Ready

0 Switch to Excel and run the macro.

■ The first cell containing the search text is activated.

FIND AND REPLACE VALUES IN CELLS

You can use the Replace method to search for and replace specific values within a specific range of cells. This method works essentially the same as the EditO Replace command in Excel.

The Replace method has several different parameters, with only two of them being required. You must specify the string for which you want to search as the value of the What parameter. You must also specify a replacement string as the value of the Replacement parameter. All remaining parameters, are optional.

You specify a value of xlWhole for the LookAt parameter to have Excel require that the What value match the entire contents of a cell before replacement. Specify xlPart if Excel should replace a cell that contains search string as part of the cell value.

Specify the appropriate XlSearchOrder constant to indicate whether to search by rows or columns. The constant xlByRows searches by rows and the constant xlByColumns searches by columns.

Type a value of True for the MatchCase parameter if Excel should only match occurrences with the same case, all uppercase, all lowercase, and so on. If you are using double-byte language support, type a value of True for the MatchByte parameter to only match double-byte characters with double-byte characters.

Using the SearchFormat and ReplaceFormat parameters is a little more complex. If either of these parameters has a value of True, you must specify the format. For the SearchFormat parameter, you specify the format properties for the Application.FindFormat object. With the ReplaceFormat parameter you specify the Application.ReplaceFormat properties. For example, to replace the text with a bold font, you can specify the following code above your Replace method:

Application.ReplaceFormat.Font.FontStyle = "Bold"

FIND AND REPLACE VALUES IN CELLS

FIND AND REPLACE VALUES IN CELLS

'-n Create a new subroutine.

B Type Application.Replace Format.Font.FontStyle = "Bold Italic", replacing "Bold Italic" with the font style to use for the replacement text.

Type Range("A1:G16").Replace, replacing Range("A1:G16") with the range to search.

'-n Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

B Type Application.Replace Format.Font.FontStyle = "Bold Italic", replacing "Bold Italic" with the font style to use for the replacement text.

Type Range("A1:G16").Replace, replacing Range("A1:G16") with the range to search.

0 0

Post a comment