Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needed
I need a macro that will auto enter the week ending date into a cell. anyone
help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needed
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. "david24" wrote: I need a macro that will auto enter the week ending date into a cell. anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needed
Thanks, the "6" needs to be an "8" though!
"JLGWhiz" wrote: 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. "david24" wrote: I need a macro that will auto enter the week ending date into a cell. anyone help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needed
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. "david24" wrote: Thanks, the "6" needs to be an "8" though! "JLGWhiz" wrote: 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. "david24" wrote: I need a macro that will auto enter the week ending date into a cell. anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro/VB Help Needed | Excel Programming | |||
Macro needed to set macro security in Excel to minimum | Excel Programming | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |