Loop NetWorkingDays

[hansgrandia]

Hello,

I'm trying to catch a weekly task in a vba code: calculating the work days between date of delivery and date of return. Sometimes, I do not have a date of return and in this situation the code should loop to the next y.

The error I got back is 1004: "Application-defined or Object-defined error"

This is the code so far. I did not build the if funtion for ignoring empty cells. Any advice how to build this into the code properly?

Regards,
Hans Grandia
Netherlands

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

Sub BreukRetourtijdTest()
Dim x As Long
Dim y As Long


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


For y = 1 To x
Cells(y, 4).Value = WorksheetFunction.NetworkDays("B & y, C & y", 0)
Next y


End Sub

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Date of delivery[/TD]
[TD]Date of return[/TD]
[TD]Work days between[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]15-12-2015[/TD]
[TD]22-12-2015[/TD]
[TD]calculated by code[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]17-12-2015[/TD]
[TD][/TD]
[TD]ignored = empty cell[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]12-1-2016[/TD]
[TD]16-2-2016[/TD]
[TD]calculated by code[/TD]
[/TR]
</tbody>[/TABLE]

 

[kvsrinivasamurthy]

Try this

For y = 1 To x

If cells(y,"C") <>"" then
Cells(y, 4).Value = WorksheetFunction.NetworkDays(Range("B" & y), Range("C" & y), 0)
End if

Next y

 

[hansgrandia]

I'm sorry...still the same error occurs. Should I change something in my settings?
Regards,
Hans

 

[kvsrinivasamurthy]

Change
For y = 1 To x
as
For y = 2 To x

 

[hansgrandia]

Thank you! Works perfectly... Regards,
Hans