Page 217 - Excel Progamming Weekend Crash Course

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

192 Saturday Afternoon

Replacing Data

You use the Replace method to replace data in a range. The syntax is:
SomeRange.Replace(What, Replacement, LookAt, SearchOrder, _
MatchCase, MatchByte)
What is the data to be replaced, and Replacement is the replacement data. The other
arguments are all optional and have the same meanings as they do for the Find method
(described earlier in this session). The Replace method always returns True, so you cannot
use its return value to determine whether any replacements were actually made.

If you need to know how many replacements are made, use the Find and
FindNext methods to count the number of times the target string appears
Tip in the range (as demonstrated in Listing 15-1). You then know how many
replacements will be made when you use the Replace method.

Replace can work with any kind of Excel data. You can replace number values, even
though the arguments to the method are strings. Here’s an example:
Selection.Replace What:=”1”, Replacement:=”9”

This code replaces all 1 digits with 9. For example:
The value 1 changes to 9.
The value 1.251 changes to 9.259.
The string 15 Oak Street changes to 95 Oak Street.
The formula =A1+1 changes to =A9+9.
The program in Listing 15-3 presents a useful example of using the Replace method.
This procedure ReplaceAllInActiveWorkbook searches all data in all worksheets in the
active workbook and makes the specified replacement. It replaces data in value cells only —
formulas are not affected (this is the default for the Replace method). The program logic is
as follows:
1. Use the ActiveWorkbook keyword to get a reference to the active workbook.
2. Loop through the Worksheets collection to get a reference to each worksheet in
the workbook.
3. For each worksheet, use the UsedRange property to obtain a range that references
the used part of the worksheet.
4. Call the Replace method on this range.