I may need to change a value of a cell to a specific value that I want, it is the “replace” function in macro so you may wondering “why to use it if I can do it without coding ?”. The macro is quicker but the best answer will be, for instance, you want to change cells with a specific date into “next coming month”, well the “replace” function will not be able to do it.
When I use the macro ?
Usually I used it to change a date value to something like “next month”.
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.
Based on cell range, for instance, to change a date value to a specific word:
Dim MyRange As Range
Dim MyCell As Range
' changethe range
Set MyRange = Range("B2:B20")
For Each MyCell In MyRange.Cells
If VarType(MyCell) = vbDate Then
' changedate if you are searching something else
If MyCell >= Int(DateSerial(Year(Date), Month(Date), 1)) Then
' change month by the new word you want to replace
MyCell = "month"
Based on column range, for instance, to change a word by another word:
Dim Rang As Range
Dim Cell As Range
' changeA:B by your column range
Set Rang = Intersect(.Columns("A:B"), .UsedRange)
For Each Cell In Rang
' change month by the word you are searching
If Cell.Value >= "month" Then
' change out of date by the new word you want to replace
Cell.Value = "out of date"