Error 1004 unable to get workclassfunction networkdays

[hansgrandia]

Hello,

In below standing code, I got stuck on a 1004 error that tells me: "unable to get worksheetfunction class networkdays". Marked in red. However, one line before this function has also been used to calculate the difference between two dates. Does someone have a suggestion how to resolve this issue?

Thank you,
Hans Grandia

.....................

Sub Test()

Application.ScreenUpdating = False

Dim x As Long
Dim y As Long
Dim z As Long
Dim NDCorBev As Double
Dim NODatstB As Double
Dim Maxvalue As Double

x = Cells(Rows.Count, 1).End(xlUp).Row

'Replace error
Columns("U:U").Select
Selection.Replace What:="0000-00-00", Replacement:="", LookAt:=xlPart, _
ReplaceFormat:=False

'Add column (F)
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"

'Note "weeknumber" in F1
Range("F1").Value = "Weeknumber"

'calculate weeknumber
For z = 2 To x
If Cells(z, "E") <> "" Then
Cells(z, 6).Value = IsoWeekNumber(Range("E" & z))
End If
Next z

'Add Column (V)
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"

'Note "werkdagen verschil" in V1
Range("V1").Value = "Werkdagen verschil"

'calculate workdays between two dates (two options possible) and take the max of those days
For y = 2 To x
NDCorBev = WorksheetFunction.NetworkDays(Range("E" & y), Range("S" & y), Worksheets("Vakantiedagen").Range("A2:A8"))
NODatstB = WorksheetFunction.NetworkDays(Range("E" & y), Range("U" & y), Worksheets("Vakantiedagen").Range("A2:A8"))

If NDCorBev >= NODatstB Then
Cells(y, 22) = NDCorBev - 1
Else
Cells(y, 22) = NODatstB - 1
End If
Next y

'delete negative figures
For y = 2 To x
If Cells(y, 22) < 0 Then
Cells(y, 22) = " "
End If
Next y

'lay out
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Application.ScreenUpdating = True

End Sub

 

[Norie]

What's the value in Range("U" & y) when you get the error?

 

[hansgrandia]

Thank you Nory. Your question brought me to the error at the beginning of the code. Now this has been resolved, the the same issue pops up the row before. ("S" & y) = 2. Which is an empty cell. Any suggestions? Thank you! Regards,
Hans

 

[hansgrandia]

Hello,
With some trail and error I found that the error was in the third argument. Simply by ignoring "holidays". Also It changed the range for the word "vakantie" (which is holiday). Please find below. Still I got the same error. Does anyone has a suggestion? Thank you! Regards, Hans Grandia - NL

......................

Dim x As Long
Dim y As Long
Dim z As Long
Dim NDCorBev As Double
Dim NODatstB As Double
Dim Maxvalue As Double

x = Cells(Rows.Count, 1).End(xlUp).Row

Set Vakantie = Worksheets("Vakantiedagen").Range("A2:A10")

[...]


NDCorBev = WorksheetFunction.NetworkDays(Range("E" & y), Range("S" & y), Vakantie)
NODatstB = WorksheetFunction.NetworkDays(Range("E" & y), Range("U" & y), Vakantie)