Network days with exclude holidays (range)

[ymeyaw]

Dear friends,

i am stucked under this place where i need to refer a list of holiday in different sheet(same workbook), how can i link that with my holiday list?

How should i link my "Holiday" to list of holiday let say in column A at sheet 2?



ElseIf StartDate <> 0 And EndDate <> 0 And RejectedDate = 0 And ResubmittedDate = 0 Then
Cells(a, "W") = Application.WorksheetFunction.NetworkDays(StartDate, EndDate,Holiday)

Thanks a lot.

 

[Andrew Poulsom]

Do you have a range named Holiday?

Cells(a, "W") = Application.WorksheetFunction.NetworkDays(StartDate, EndDate,Range("Holiday"))

Note that NetworkDays was not a WorksheetFunction prior to Excel 2007. It was in the Analysis ToolPak Add-In.

 

[ymeyaw]

Hi Sir,

I keep having subsricption out of range when i have the following range list. Where is my problem



HolidayList = ActiveWorkbook.Worksheets(Holiday).Range("A1:A10")
|
|
|


ElseIf StartDate <> 0 And EndDate <> 0 And RejectedDate = 0 And ResubmittedDate = 0 Then
Cells(a, "W") = Application.WorksheetFunction.NetworkDays(StartDate, EndDate, Range("HolidayList"))

 

[Richard Schollar]

You don't reference it like that - you just need:

ElseIf StartDate <> 0 And EndDate <> 0 And RejectedDate = 0 And ResubmittedDate = 0 Then
Cells(a, "W") = Application.WorksheetFunction.NetworkDays(StartDate, EndDate, Worksheets("Holiday").Range("A1:A10"))

 

[Andrew Poulsom]

First:

HolidayList = ActiveWorkbook.Worksheets("Holiday").Range("A1:A10").Value

Then:

Cells(a, "W") = Application.WorksheetFunction.NetworkDays(StartDate, EndDate, HolidayList)

 

[ymeyaw]

Hi Andrew,

Thanks alot!! i know where my syntax error is now...Have a nice day!!!!

 

[ymeyaw]

Hi Richard,

This work wells too. everything in one line Thanks alot!!!