Excel VBA Macro

Question

Hello. Please help me if you can. It's that time of year. EOY processing and New Year processing.

I have a routine where I would like to set the page number to "1" if the current or today's date is December 31 or January 1 or January 2. If not any of these, add +1 to the current page number.

The page number is located on my excel spreadsheet in cell L1.

I've coded the following but there isn't an easy way to check today's month and today's date.

Thank you for any assistance you can provide.

L1 is the cell where the page number is displayed:

' If (Month(TODAY()) = 12 And Day(TODAY()) = 31) Or _

' (Month(TODAY()) = 1 And Day(TODAY()) = 1) Or _

' (Month(TODAY()) = 1 And Day(TODAY()) = 2) Then

' Range("L1").Value = 1

' ElseIf Range("L1").Value > 0 Then

' Range("L1").Value = Range("L1").Value + 1

' Else

' Range("L1").Value = 1

' End If

Answer 1

Thank you. Very interesting solution!

Answer 2

Hi. Thank you for your suggestion. It will work.

Answer 3

Sub Test()
'If today is between December 31 and January 2 last/this year or this/next year
If (Date >= DateSerial(Year(Now) - 1, 12, 31) And Date <= DateSerial(Year(Now), 1, 2)) Or _
(Date >= DateSerial(Year(Now), 12, 31) And Date <= DateSerial(Year(Now) + 1, 1, 2)) Then
Range("L1") = 1
Else
Range("L1") = Range("L1") + 1
End If
End Sub

Answer 4

Hi,

try this

[edit.. ]

Sub macro_01()

Dim Date1, Date2

Date1 = DateSerial(2022, 12, 31) ''<< Dec 31st 2022

Date2 = Date1 + 2 '<< Jan 2nd 2023

Dim r

Set r = Range("L1")

If Date >= Date1 And Date <= Date2 Then

r.Value = 1

ElseIf r.Value > 0 Then

r.Value = r.Value + 1

Else

r.Value = 1

End If

End Sub