Replace a word by another one using a macro in an excel report

If you are going to change a word, for instance, done by another word, for instance, undone once a year, it is best to use the replace function. I use this code because I need to do it daily, much quicker and easier.

formula excel formula excel formula excel


When I use the macro ?

To change a specific word by another one.


How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report


How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report


How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

This code changes the word or phrase only in the selected single column or a range of selected columns:

Sub test()
' change the range ie C:C, F:M, etc.
' change new and old, it can be a word or a phrase
Selection.Replace What:="new", Replacement:="old"
End Sub

This code changes the word or phrase from a cell of a specific column to another cell of a specific column:

Sub test()
' search from row 2 until 50
For i = 2 To 50
' in column B, change old by the word/phrase to search
' in column C, change out of date by the word/phrase to replace
If Range("B" & i).Value = "old" Then _
' for multiple columns ("C" & i & ":D" & i)
Range("C" & i).Value = "out of date"
Next i
End Sub

Interesting Management