Macro 53: Append Text to the Left or Right of Your Cells
Every so often, you may come upon a situation where you need to attach data to the beginning or
end of the cells in a range. For instance, you may need to add an area code to a set of phone num-
bers. This macro demonstrates how you can automate the data standardization tasks that require
appending data to values.
How it works
This macro uses two Range object variables to go through the target range, leveraging the For
Each statement to activate each cell in the target range. Every time a cell is activated, the macro
attaches an area code to the beginning of the cell value.
Sub Macro53()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Ensure the cell has Text formatting.
If Not IsEmpty(MyCell) Then
MyCell = “(972) ” & MyCell
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as we enumerate through
them one by one.
2. When you run a macro, it destroys the undo stack. This means you can’t undo the changes a
macro makes. Because we are actually changing data, we need to give ourselves the option
of saving the workbook before running the macro. This is what Step 2 does. Here, we call up
a message box that asks if we want to save the workbook first. It then gives us three choices:
Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking
Cancel exits the procedure without running the macro. Clicking No runs the macro without
saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as we go
through.
5. After a cell is activated, we use the ampersand (&) to combine an area code with the cell
value. If you need to append text to the end of the cell value, you would simply place the
ampersand and the text at the end. For instance, MyCell = MyCell & “Added Text”.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
