Searching and Replacing Dates

Closed
FranBacAero Posts 1 Registration date Tuesday August 9, 2016 Status Member Last seen August 9, 2016 - Aug 9, 2016 at 02:43 PM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Aug 11, 2016 at 12:05 PM
I need to search and replace dates. For example, in one row (representing one employee), all dates prior to 8/9/16 and replace those dates with 8/9/16. I've tried search and replace, but that isn't quite it. It doesn't recognize the "less than" symbol. Is there an "if ... then... " statement and how would I use it?

Time sensitive problem.

Thank you!

1 reply

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 11, 2016 at 12:05 PM
Hi FranBacAero,

Assuming the dates are located in one column, try this (after changing the 3 A's on the 2nd code line to the column letter referring to your date column):
Sub RunMe()
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value <> vbNullString And cell.Value < Now Then
        cell.Value = Format(Now, "m-d-yy")
    End If
Next cell
End Sub


In case you don't know:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.



Best regards,
Trowa
0