Show and tell: Last-Yearify (Happy new year! )
I work in accounts, and I'm still entering transactions for 2024. If you enter just day and month into excel, it will assume this year. I get to the bottom of a column and see I've accidentally missed the year of a few dates and need to correct them.
I got frustrated and made the following.
Sub LastYearify()
' Purpose: Checks if the selected cell is a date, and pushes that date into last year.
' Origin: Created by Joseph in 2024. No wait, 2025.
Dim thisCell As Integer
Dim CellCount As Integer
Dim myRange As Range
Dim myCell As Range
On Error GoTo Errorhandler
Set myRange = Application.Selection
CellCount = myRange.Cells.Count
For thisCell = 1 To CellCount
Set myCell = myRange.Cells(thisCell)
If IsDate(myCell.Value) Then
myCell.Value = DateSerial(Year(Now()) - 1, Month(myCell.Value), Day(myCell.Value))
Else
Debug.Print myCell.Address & " - Not a date."
End If
Set myCell = Nothing
Next thisCell
Exit Sub
Errorhandler:
MsgBox ("There has been an error. Sorry.")
End Sub