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

Customer	Date of delivery	Date of return	Work days between
A	15-12-2015	22-12-2015	calculated by code
B	17-12-2015		ignored = empty cell
C	12-1-2016	16-2-2016	calculated by code

<tbody>
</tbody>

 

[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