VBA Code to Find and Replace Multiple Terms in Specified Range

Tonight's tip provides VBA code to find and replace multiple terms in a specified range. I've edited this down from the code that Dave3009 posted to this site.

In this example I am using the code to standardize references to trial exhibits listed in a column of a worksheet. I don't want the macro to alter text that may be entered elsewhere on the worksheet, so I am specifying that text only be edited in column A. See the reference on the lines which begin 'Columns'. In addition to the terms that are found and replaced on those lines, you also need to specify the worksheet name on the third line.

In this example I have a list of trial exhibits. Some of the exhibit references have been entered with a space between the letter prefix and the number, and some have not. I want a space between 'DX' or 'PX' and the number in each as I have my PDFs named this way. First I add a space after the letter prefixes, and then I remove those letter prefixes which are left being followed with two spaces. The code goes in order with subsequent lines correcting the changes made by earlier lines.

As always press ALT + F11 to go into Visual Basic. Then enter the code below in a new module, and edit as necessary. When the code is run you will get a nice little message (that you can also edit on the second to last line), and in this example you can see the exhibit references are indeed standardized.

Private Sub CommandButton1_Click() Dim rws As Long With Sheets("Sheet1") rws = .UsedRange.Rows.Count .Columns("A:A").Replace What:="T", Replacement:="-T" .Columns("A:A").Replace What:="DX", Replacement:="DX " .Columns("A:A").Replace What:="DX ", Replacement:="DX " .Columns("A:A").Replace What:="PX", Replacement:="PX " .Columns("A:A").Replace What:="PX ", Replacement:="PX " .Columns("A:A").Replace What:="GX", Replacement:="GX " .Columns("A:A").Replace What:="GX ", Replacement:="GX " End With MsgBox "Trial Exhibit References Standardized!" End Sub