Getting things to happen in particular months

[Red over White]

I am trying to write a VBA program where actions are dependent on what the current month is. In the example below, in February, May, August or November one set of actions will happen and in the remaining months another set of actions will occur.
If the macro was run today, I would be looking for the program to be picking up on the “=11” element.
What I’m struggling what is what the correct TODAY/MONTH combination for this to happen, bearing in mind I use UK date formats. I have tried various combinations of TODAY, MONTH and DATE, with and without brackets, but have not had any success

If TODAY/MONTH combination = 2 Or 5 Or 8 Or 11 Then
One set of actions
End if
If TODAY/MONTH combination <> 2 Or 5 Or 8 Or 11 Then
Another set of actions
End if

If someone does come up with a solution, I would be grateful if they could clarify if I need to put leading zeroes in front of the single digit months in readiness for next year.

Thanks

 

[Proulxs]

If you want the month of today you can use
If Month(Now) = 11 Or 8 Or 5 Or 2 Then
set of action
Else
Set of another action
End if

Your date format should not be relevant as Month formula will return a number from 1 to 12.
And with else it wil ltrigger for all case that are not already accounted for. No need to do a 2nd if this way.

 

[Red over White]

Thanks for the response, but having copied that line into my macro I'm getting "Compile error - Wrong number of arguments or invalid property assignment" with Month highlighted

 

[RobP]

Hi,

does this help you ?

Sub test()

this_month = Month(Now())

Select Case this_month

    Case 2, 5, 8, 11
        MsgBox ("2,5,8,11")
    Case 1, 3
        MsgBox ("1,3")
    Case 4, 9
        MsgBox ("4,9")
    Case Else
        MsgBox ("anything else goes here...")
    
End Select

End Sub

 

[Proulxs]

When in VBA if you type the formula manually when you open the parenthese it show you how many argument. For me it should be only one; the date. Now being the date and hour of when the vba is running

If this does not work for you try adding an opening and closing parenthese after Now.

 

[Red over White]

Hi,

does this help you ?

Sub test()

this_month = Month(Now())

Select Case this_month

    Case 2, 5, 8, 11
        MsgBox ("2,5,8,11")
    Case 1, 3
        MsgBox ("1,3")
    Case 4, 9
        MsgBox ("4,9")
    Case Else
        MsgBox ("anything else goes here...")
   
End Select

End Sub

I'm getting the same error message as before with Month again highlighted

 

[RobP]

As @Proulxs mentioned.. do you have the correct number of brackets ? else a reboot, as not sure why it would stop for you ?

 

[Red over White]

As @Proulxs mentioned.. do you have the correct number of brackets ? else a reboot, as not sure why it would stop for you ?

I attach a copy of the two macros, and the error message that I get on the second macro. It doesn't appear to like "month", I have tried capitalising the "M", but it reverts to lower case.

 

[RobP]

So the error is in your own macro, rather than in the code I sent you ?

Can you run my code and see what you get please ?

 

[Red over White]

I get the same error message on both macros.