Auto_Open() How to show prior Monday?

[Guest]

This code gets me the 1st day of the current workweek. I need to edit it to
get the 1st day of the previous workweek.

Sub Auto_Open()
' Enter the prior Monday
Set StartDay = Sheets("SamB").Range("StartDay")
StartDay.Value = Date
Do Until Weekday(StartDay) = 2
StartDay.Value = StartDay.Value - 1
Loop
End Sub

I would appreciate your help.
Thanks,

Jeff

 

[Harald Staff]

Hi Jeff

Sub MakePevMonday()
Dim D As Date
D = Date - Weekday(Date, vbMonday) - 6
Sheets("SamB").Range("StartDay").Value = D
End Sub

HTH. Best wishes Harald

 

[Guest]

Hi Harald,
Thank You, this worked.
May I ask?, What is "Dim" and "vbMonday" or how do they work?
I am pretty new to excel but finding myself addicted to it. I am reading up
as much as I can but some of this stuff is pretty easy and some of it goes
way over my head.
Thanks again, I realy appreciate your help.

Jeff

 

[Harald Staff]

Hi Jeff

Dim is important, it declares the variables to be used. See any book on
programming or do a Google search on the topic "declare variables", it's
really essential programming and too big a topic to be reasonably described
here. This one seem like a good start
http://www.beyondtechnology.com/vba008.shtml

vbMonday, on the other hand, is a part of the Weekday function in Vb,
defining which weekday is number 1. Not important at all in the big picture.

HTH. Best wishes Harald