Macro needed

D

david24

I need a macro that will auto enter the week ending date into a cell. anyone
help?
 
J

JLGWhiz

Try this:

Sub lastDOW()
dif = "6" - Format(Date, "w")
lstDOW = Format(Now + dif, "d mmm yyyy")
ActiveSheet.Range("A1") = CDate(lstDOW)
End Sub

It posts the date in A1 in the sytem format as data type Date. You can
either format the cell to change it or modify the code.
 
D

david24

Thanks, the "6" needs to be an "8" though!

JLGWhiz said:
Try this:

Sub lastDOW()
dif = "6" - Format(Date, "w")
lstDOW = Format(Now + dif, "d mmm yyyy")
ActiveSheet.Range("A1") = CDate(lstDOW)
End Sub

It posts the date in A1 in the sytem format as data type Date. You can
either format the cell to change it or modify the code.
Click to expand...
 
J

JLGWhiz

There are only seven days in a week. How can you use 8 and get an answer
that gives a result for Friday which is day number 6.

Friday - Thrusday = 1, Thursday + 1 = Friday
Eight - Thursday = 3, Thursday + 3 = Sunday
Eight - Friday = 4, Friday + 4 = 10 which will give the
wrong date for the end of week day.

The code should subtract the current day of the week from the end of the
week day. This gives the number of days left until the end of the week.
Then add that number to the current date to get the date for the end of week
day.

Maybe I did not understand the question.
 
Top