Question about a custom defined function
 

i have the following function which supposed to take a date and check it
against a lookup sheet that i have with flags whether the date five days
after the initial is weekend or bank holiday. if it is then it rolls a day
and does the checks again.

my problem is that the function goes to the first do and then when it
reaches the second do it exits

anyone can help me with this...


Public Function returndate(inidate As Date) As Date

Dim DatetoReturn As Date
Dim BHol, WkEnd As Boolean
Dim fstresult As String
Dim sstresult As Integer

BHol = True
WkEnd = True

DatetoReturn = inidate + 5

Do

Do While BHol = True
fstresult = Application.VLookup(DatetoReturn, Sheets("Dates
Lookup").Range("A:D"), 4)
If fstresult = "yes" Then
DatetoReturn = DatetoReturn + 1
Else
BHol = False
End If
Loop

Do While WkEnd = True
ssetresult = Application.VLookup(DatetoReturn, Sheets("Dates
Lookup").Range("A:D"), 2)
If sstresult = 6 Then
DatetoReturn = DatetoReturn + 2
ElseIf sstresult = 7 Then
DatetoReturn = DatetoReturn + 1
Else
WkEnd = False
End If
BHol = True
Loop

Loop Until BHol = False And WkEnd = False

returndate = DatetoReturn

End Function

Question about a custom defined function
 

i have identified that it exits when i try to do the vlookup function within
the loop.

"Panagiotis Marantos" wrote:

i have the following function which supposed to take a date and check it
against a lookup sheet that i have with flags whether the date five days
after the initial is weekend or bank holiday. if it is then it rolls a day
and does the checks again.

my problem is that the function goes to the first do and then when it
reaches the second do it exits

anyone can help me with this...


Public Function returndate(inidate As Date) As Date

Dim DatetoReturn As Date
Dim BHol, WkEnd As Boolean
Dim fstresult As String
Dim sstresult As Integer

BHol = True
WkEnd = True

DatetoReturn = inidate + 5

Do

Do While BHol = True
fstresult = Application.VLookup(DatetoReturn, Sheets("Dates
Lookup").Range("A:D"), 4)
If fstresult = "yes" Then
DatetoReturn = DatetoReturn + 1
Else
BHol = False
End If
Loop

Do While WkEnd = True
ssetresult = Application.VLookup(DatetoReturn, Sheets("Dates
Lookup").Range("A:D"), 2)
If sstresult = 6 Then
DatetoReturn = DatetoReturn + 2
ElseIf sstresult = 7 Then
DatetoReturn = DatetoReturn + 1
Else
WkEnd = False
End If
BHol = True
Loop

Loop Until BHol = False And WkEnd = False

returndate = DatetoReturn

End Function