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:
Sub test()
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"
End If
End If
Next MyCell
End Sub
Based on column range, for instance, to change a word by another word:
Sub test()
Dim Rang As Range
Dim Cell As Range
With ActiveSheet
' changeA:B by your column range
Set Rang = Intersect(.Columns("A:B"), .UsedRange)
End With
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"
End If
Next Cell
End Sub
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...