A procedure is an effective way of cutting down the repetition of manually writing blocks of code you will be frequently using throughout your source code.
The below example will show different methods of writing a Find & Replace procedure in VBA to suit your needs and how to call it within another macro.
The procedure we will be creating will use the Range.Replace method, below is a summary of the regular expression and the specific parameters that we will be using for our needs. You can read more about this expression and see the full list of parameters here.
Expression: Replace( What , Replacement , LookAt , SearchOrder , MatchCase , SearchFormat , ReplaceFormat )
Parameters
| Name | Required/Optional | Description |
|---|---|---|
| What | Required | The string you want Microsoft Excel to search for. |
| Replacement | Required | The replacement string. |
| LookAt | Optional | Can be one of the following XlLookAt constants: xlWhole or xlPart . |
| SearchOrder | Optional | Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns . |
| MatchCase | Optional | True to make the search case sensitive. |
| SearchFormat | Optional | The search format for the method. |
| ReplaceFormat | Optional | The replace format for the method. |
Find & Replace By Column
'Find and Replace by a specific Column.
Sub FindReplaceCol(col As String, findString As String, replaceWithString As String)
Columns(col).Replace What:=findString, _
Replacement:=replaceWithString, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Called with
Call FindReplaceCol("A", "replacethistext", "with this text") 'Find and Replace within column A.
Find & Replace By Row
'Find and Replace by a row.
Sub FindReplaceRow(row As Integer, findString As String, replaceString As String)
rows(4).Replace _
What:=findString, _
Replacement:=replaceString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Called with
Call FindReplaceRow(4, "replacethistext", "with this text") 'Find and Replace within row 4.
Find & Replace By Range
'Find and Replace by a specific Range.
Sub FindReplaceRange(cell1 As String, cell2 As String, findString As String, replaceString As String)
Range(cell1, cell2).Replace _
What:=findString, _
Replacement:=replaceString, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Called with
Call FindReplaceRange("A2","E10", "replacethistext", "with this text") 'Find and Replace within range A2:E10.