Win10 Pro with O365.
I'm new the this company as IT manager and have inherited a lot of stuff. We had an Access programmer that was only OK. I've got no access to Access (sorry) to modify any of the code, so I have to use as is.
I've automated with Power Automate an Access program that a user ran first thing in the am. By having it run before she gets in, I'm saving her about an hour and everyone in the warehouse starts an hour earlier. Total win for them. I basically saved the company about 12 hours the first day it ran. ROI is less than a week.
The program runs Monday through Friday at 3:00 am and is date driven. The program asks for a date at 4 different times. Currently, I've got a spreadsheet with:
=IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1)
If today is Monday, then subtract 3 for last Friday; otherwise, subtract 1 for yesterday. Works like a charm.
Our company observes 10 holidays every year. Those dates will change each year.
Goal:
To write VBA code in my spreadsheet to accommodate for those specific dates.
In a testing spreadsheet, I've got E2 thru E11 formatted as date. Each cell has the work day following the specific holiday.
2/22/2022 was Tuesday following President's Day. My formula would need last Friday's date or today()-4 or 2/18/2022.
To test, I change my system date to the day after a holiday and run the macro. It only returns the date of today()-4 regardless of the system date. I'm missing something quite easy, just can't see the forest thru the trees.Sub Holiday() Dim dtToday As Date dtToday = Date If dtToday = E2 Then Range("A1") = Date - 4 ElseIf dtToday = E3 Then Range("A1") = Date - 4 ElseIf dtToday = E4 Then Range("A1") = Date - 4 ElseIf dtToday = E5 Then Range("A1") = Date - 4 ElseIf dtToday = E6 Then Range("A1") = Date - 4 ElseIf dtToday = E7 Then Range("A1") = Date - 4 ElseIf dtToday = E8 Then Range("A1") = Date - 5 ElseIf dtToday = E9 Then Range("A1") = Date - 4 ElseIf dtToday = E10 Then Range("A1") = Date - 4 Else dtToday End If 'If dtToday = Date Then 'Range("A1") = "Howdy" 'End If End Sub
I'd hardcode the dates in like:
If dtToday = 2/22/2022 Then
Range("A1") = Date - 4
But VB adds spaces to the date - 2 / 22 / 2022 and hoses the code (or appears to).
Once working, I'd like to have the macro run when the sheet is opened; but I can work around that.
Thanks for the help.