I'm writing a function to normalize a date based on a simple table of review dates. If a date is between review date and the valid date, it should be normalized to the review date.
Review Date Valid Until
01-01-1900 14-04-2009
15-04-2009 14-04-2010
15-04-2010 31-12-9999
Following formula in Excel gives correct result '=VLOOKUP(D5,$A$5:$A$7,1,TRUE)' where the D5 contains a date to be normalized and $A$5:$A$7 is the review dates column.
However when I'm trying to replicate this with a following code in VBA the function:
Function ReviewDate(start_date As Date) As Date
Dim dteStartDate As Date
Dim varReviewDate As Variant
dteStartDate = start_date
varReviewDate = Sheets("ReviewTable").Range("$A$5:$A$7").Value
ReviewDate = WorksheetFunction.VLookup(dteStartDate, varReviewDate, 1, True)
End Function
ReviewDate returns different results e.g. ReviewDate("15-04-2009")=15-04-2010.
I suspect some date conversion issue I've already used CDate to conver dates but no success... Attached files contains the code, and a test
Any idea what goes wrong?
Cheers
Artek
Bookmarks