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.
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/are the macro(s) ?
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. Range("B:B").Select ' 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 Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...