Hi guys.
I'm relatively new with VBA. and I'm trying to get a code to run 7 business days before a certain date. is it possible to do something like this?
thankx for any help
Hi guys.
I'm relatively new with VBA. and I'm trying to get a code to run 7 business days before a certain date. is it possible to do something like this?
thankx for any help
Add some code to the workbook open event to check if today's date is 7 days before that date.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
It has been pointed out to me that I missed the business days part. So I will be a bit more detailed
[vba]
Private Sub Workbook_Open()
Dim TargetDate As Date
TargetDate = DateSerial(2011, 2, 28)
If Date = Application.Evaluate("Workday(" & CLng(TargetDate) & ",-7)") Then
MsgBox "We're here"
End If
End Sub
[/vba]
This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hey.
thanks for your help. is there a way to add like 2 days or something. I have a column of dates and I need the code to run if Today = one of the dates in the column. but I'm not at work in the weekend so it'll only work during the week.
so if I could just add two days to any weekend date, that'd solve my problem.
thank you
[vba]
TestDate = Date
IF Weekday(Date) = 1 Then
TestDate = TestDate + 1
ElseIf WeekDay(Date) = 7 Then
TestDate = TestDate + 2
End If
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber