• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Workday function in VBA code

R

Ria

Member
Hi all:

I am struggling with workday function in VBA using Excel 2003. Finally decided to ask masters. Here is my requirement:
Column A has dates (format: 24/12/2014).
Other cell: M2, has value: 11, it may change.
Need VBA code: a) read last row (e.g. R503) in column A and its value (which is date: 24/12/2014).
b) in last date (24/12/2014) add one (e.g. 24/12/2014 + 1) and get next date (EXCLUDING weekend (sat & sun) and EXCLUDE other stat holidays (list of holidays is in same workbook, worksheet: Holidays (column A, no heading).
c) put next date in next row e.g. R504 (column A, e.g. R503 +1)
d) loop through/repeat above process 11 times (or till M2 = 11) and keep filling next row of column A with next date.

I have following code that does all work but I am unable to exclude weekend and other holidays. I know how to use WORKDAY function in excel but unable to handle in VBA.
Any proven solution will be highly appreciated. Know less about VBA.

My working code:
Code: 
Sub nextDate ()
  Dim FLD_Count As Integer, I As Integer, LR As Long, LRDate As Long, nextRow As Long, nextDate As Long
 
  FLD_Count = ActiveSheet.Range("M2").Value
  LR = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row
  LRDate = ActiveSheet.Range("A" & LR).Value
 
  ' Begin the loop.
  For I = 1 To FLD_Count
  ActiveSheet.Range("A" & LR + I) = LRDate + I
 
  ' ActiveSheet.Range("A" & LR + I) = Workday(LRDate, LRDate + I)
  Next I
 
End Sub

Thanks

Ria
 
Hi ,

See if this works :
Code: 
Sub nextDate()
    Dim FLD_Count As Integer, I As Integer, LR As Long, LRDate As Long
   
    FLD_Count = ActiveSheet.Range("M2").Value
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    LRDate = ActiveSheet.Range("A" & LR).Value
'  Begin the loop.
    For I = 1 To FLD_Count
        ActiveSheet.Range("A" & LR + I) = Application.WorksheetFunction.WorkDay(LRDate, I, [StatHolidays])
    Next I
End Sub
StatHolidays is a named range which refers to the range having the holiday dates.

Narayan
 
NARAYANK991 said:
Hi ,

See if this works :
Code: 
Sub nextDate()
    Dim FLD_Count As Integer, I As Integer, LR As Long, LRDate As Long
  
    FLD_Count = ActiveSheet.Range("M2").Value
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    LRDate = ActiveSheet.Range("A" & LR).Value
'  Begin the loop.
    For I = 1 To FLD_Count
        ActiveSheet.Range("A" & LR + I) = Application.WorksheetFunction.WorkDay(LRDate, I, [StatHolidays])
    Next I
End Sub
StatHolidays is a named range which refers to the range having the holiday dates.

Narayan
Click to expand...
Thanks Narayan:

I got error message at following line:
Code: 
ActiveSheet.Range("A" & LR + I) = Application.WorksheetFunction.Workday(LRDate, I, [StatHolidays])
Runtime error 438.
Object does not support this property or method.

Even I change holiday sheet name to StatHolidays and created range name: StatHolidays. But still getting errors. What I am doing wrong here?

Thanks

Ria
 
Somendra Misra said:
Hi Ria,

@NARAYANK991 code works perfect. You have to define named range for holidays date list as shown in below link:

http://support.microsoft.com/kb/830287

Regards,
Click to expand...
Thanks Narayan:
As per instructions in link, defined named range for holiday and named it StatHolidays, I can not name it as Date because Date is already named for other uses. Then in vba tried to use it as: StatHolidays and then as [StatHolidays], but get same error message. When I hover qurser on yellow line, it shows StatHolidays = empty. I know your logic is valid and I used it before elsewhere but right now, something is missing or I am doing wrong.
Here is in Excel 2003, how I defined
  1. On the Insert menu, point to Name, and then click Define.
  2. In the Names in workbook box, type StatHolidays.
  3. In the Refers to box, type the following text, and then click OK:
    =OFFSET($A$1,0,0,COUNTA($A$1:$A$200),1)
You may guide further.

Thanks,

Ria
 
@Ria

You mentioned your list of holidays are on sheet Holidays if so than your formula should be:

=OFFSET(Holidays!$A$1,0,0,COUNTA(Holidays!$A$1:$A$200),1)

Try this.

Regards,
 
Somendra Misra said:
@Ria

You mentioned your list of holidays are on sheet Holidays if so than your formula should be:

=OFFSET(Holidays!$A$1,0,0,COUNTA(Holidays!$A$1:$A$200),1)

Try this.

Regards,
Click to expand...
Sorry Narayan:
Error is still there. I know third argument in workday function is optional e.g. statholidays. If I remove third argument from vba code and run it, error is still there.

Any other thoughs?

Thanks,

Ria
 
Somendra Misra said:
I think you are working on 2003, if so than refer comment # 5 on below link.

http://www.mrexcel.com/forum/excel-questions/608584-workday-function-visual-basic-applications.html

Regards,
Click to expand...
Hi Narayan:
Finally, found solution and it is working fine. Here is what I found:
In Excel 2003/Tools/Add-Ins/check: Analysis Toolpack, Analysis ToolPack-VBA & Solver Add-In &
' In VBE, Tools/References/check: atpvbaen.xls, to make WORKDAY FUNCTION WORKING.
Only problem is it does not accept named range in vba code and we have to specify sheet name and range directly instead of named range.
I am happy it is working.

Here is the code line causing problem and now working:
Code: 
ActiveSheet.Range("A" & LR + I) = Workday(LRDate, I, Worksheets("Holidays").Range("A1:A1000"))

Thanks for guidance.

Ria
 
You must log in or register to reply here.
Back
Top