Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
With VBA I need to detect the last weekday of each month. I need that to trigger an event macro on the last workday of each month.
Regards Cimjet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Sun, 23 Sep 2012 15:19:47 -0700 (PDT), Cimjet wrote:
With VBA I need to detect the last weekday of each month. I need that to trigger an event macro on the last workday of each month. Regards Cimjet To detect the last workday of the month in VBA, you can use: Dim dLastWDOM As Date dLastWDOM = WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1) If Date = dLastWDOM Then ... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Sunday, September 23, 2012 8:25:27 PM UTC-4, Ron Rosenfeld wrote:
On Sun, 23 Sep 2012 15:19:47 -0700 (PDT), Cimjet wrote: With VBA I need to detect the last weekday of each month. I need that to trigger an event macro on the last workday of each month. Regards Cimjet To detect the last workday of the month in VBA, you can use: Dim dLastWDOM As Date dLastWDOM = WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1) If Date = dLastWDOM Then ... Thank you Ron Just could not get my head around it. Much appreciated. John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Sun, 23 Sep 2012 17:43:29 -0700 (PDT), Cimjet wrote:
Thank you Ron Just could not get my head around it. Much appreciated. John Glad to help. But please note that if you will be using this in versions of Excel prior to 2007, a different algorithm will be required, as the Workday was part of the Analysis ToolPak (and not a built-in worksheetfunction) prior to Excel 2007. Post back if that might be an issue. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Monday, September 24, 2012 3:18:37 PM UTC-4, Ron Rosenfeld wrote:
On Sun, 23 Sep 2012 17:43:29 -0700 (PDT), Cimjet wrote: Thank you Ron Just could not get my head around it. Much appreciated. John Glad to help. But please note that if you will be using this in versions of Excel prior to 2007, a different algorithm will be required, as the Workday was part of the Analysis ToolPak (and not a built-in worksheetfunction) prior to Excel 2007. Post back if that might be an issue. Hi Ron You read my mind, I'm using XL03 and looking at your formula, I was sure it would work,it's almost the same as the cell function but no luck I still need your help. Thanks John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Mon, 24 Sep 2012 16:35:19 -0700 (PDT), Cimjet wrote:
Hi Ron You read my mind, I'm using XL03 and looking at your formula, I was sure it would work,it's almost the same as the cell function but no luck I still need your help. Thanks John OK, let me see if I can remember this. And I can't test it as I don't have Excel 2003 installed. In Excel 2003, the WORKDAY function is part of the Analysis Tool Pak. To use it in VBA, do the following: Open Excel; and select Tools/Add-Ins Select: Analysis ToolPak - VBA (If that is not there, you will have to browse for it; try something like C:\Program Files\Microsoft Office\Office\Library or do a search) Then open the VBA Editor. Select Tools/References Select the reference to atpvbaen.xls You should now be able to use the Workday function directly in your VBA macro. So, in the macro, remove the "worksheetfunction." before the Workday, so it looks like: dLastWDOM = WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Monday, September 24, 2012 9:05:04 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 24 Sep 2012 16:35:19 -0700 (PDT), Cimjet wrote: Hi Ron You read my mind, I'm using XL03 and looking at your formula, I was sure it would work,it's almost the same as the cell function but no luck I still need your help. Thanks John OK, let me see if I can remember this. And I can't test it as I don't have Excel 2003 installed. In Excel 2003, the WORKDAY function is part of the Analysis Tool Pak. To use it in VBA, do the following: Open Excel; and select Tools/Add-Ins Select: Analysis ToolPak - VBA (If that is not there, you will have to browse for it; try something like C:\Program Files\Microsoft Office\Office\Library or do a search) Then open the VBA Editor. Select Tools/References Select the reference to atpvbaen.xls You should now be able to use the Workday function directly in your VBA macro. So, in the macro, remove the "worksheetfunction." before the Workday, so it looks like: dLastWDOM = WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1) Hi Ron This time it's tried and tested and it works great. I knew about the Analysis ToolPak and atpvbaen.xla, I just couldn't get the syntax right. Thanks again John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last weekday of the month with vba for all months
On Mon, 24 Sep 2012 19:17:45 -0700 (PDT), Cimjet wrote:
This time it's tried and tested and it works great. I knew about the Analysis ToolPak and atpvbaen.xla, I just couldn't get the syntax right. Thanks again John Glad to help. Thanks for the feedback. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to list dates in a month of particular weekday | Excel Programming | |||
find date of last weekday of a month | Excel Discussion (Misc queries) | |||
Weekday formula calculating to end of month | Excel Worksheet Functions | |||
nth weekday of the the month date problem | Excel Programming | |||
Xth Weekday of the Month/Year | Excel Programming |