Replacing Values with Excel VBA

The Replace method of the Range object performs replacements within a specified range.

Replace(What, Replacement, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

• What — required parameter specifying the string to be replaced.
• Replacement — required parameter specifying the string to replace with.
• LookAt — optional parameter indicating how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order in which to search the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case in the search.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.
• ReplaceFormat — optional parameter specifying the replacement format.

For example, the following code replaces the string « MS » with « Microsoft » in column A:

Columns("A").Replace What:="MS", Replacement:="Microsoft", _
    SearchOrder:=xlByColumns, MatchCase:=True