No correct result of user defined function

[jostoorged]

Hi,

I've made an worksheet which I use for planning.
In the first 2 columns the workpart is discribed, in the 3th the number of mechanics, the 4th the needed hours and the 5th the startingdate.

In the Rows 12,13 en 14 the weeknumber, date and short weekday are noted past the 5th column.

Then I've made a new Fuction in VBA.
When the date in row 13, above the cell with this function, equals the date in column 5 from the same row in which the function stands, than the result must be the length of the job.

The function vulling()

Public Function Vulling(datum1 As Date, datum2 As Date, uren As Integer, ploeg As Integer, soort As String) As String
'datum1 = startdate
'datum2 = date in Row 13 above cell with function
'uren = the hours needed for the job
'ploeg = crew consisting of 2 mechanics
Dim lengte As Integer 'the number of working days that the job takes

If uren > 0 Then
lengte = uren / (ploeg * 8)
End If

For i = 0 To lengte
datum1 = DateAdd("d", i, datum1)
If datum1 = datum2 Then
Vulling = lengte
End If
Next i

End Function

The following strange results occure. The correct result is diplayed when "lengte" isn't bigger than 1.

But when it's > 1 than Excel gives different results with the same number of uren and ploeg.

For instance when uren = 16 and ploeg = 1 datum1 = "7-3"
The result should be a "2" displayed in the cell with the date datum2 = "7-3" and the cell with "8-3" above.
This is the case but there's also a "2" in the cell with "10-3" above

Strangely enough there are also rows wich display the correct result.
But when lengte becomes greater the results become the more stranger.



When I use the same code in a Macro and type the vars. in a Inputbox and display the results in a Msgbox the results are always correct.

Can anybody help me,

If you send me an e-mail i can send you the worksheet.

[email protected]
[email protected]

[Leith Ross]

Hello Jostoorged,

You don't exit your loop after the dates are equal. This is most likely what is causing your problem. Make the following changes to your loop, and it should work fine unless you have a problem elsewhere outside the macro.

For i = 0 To lengte
datum1 = DateAdd("d", i, datum1)
If datum1 = datum2 Then
Vulling = lengte
Exit Function
End If
Next i

End Function

Hope this helps,
Leith Ross

[jostoorged]

Made the adjustment, ofcorse it "speeds up" the loop, but still the strange results.

But thanks,

Could it be a problem of the older Excel 97 and 2000?
Because I use the 97 vs at work.

Jos