Results 1 to 4 of 4

Worksheet function Vlookup and VLOOKUP return different results

Threaded View

zandero Worksheet function Vlookup... 08-08-2010, 04:32 AM
Marcol Re: Worksheet function... 08-08-2010, 07:44 AM
zandero Re: Worksheet function... 08-08-2010, 08:10 AM
Marcol Re: Worksheet function... 08-08-2010, 08:24 AM
  1. #1
    Registered User
    Join Date
    08-08-2010
    Location
    Krakow, Poland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Worksheet function Vlookup and VLOOKUP return different results

    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
    Attached Files Attached Files
    Last edited by zandero; 08-08-2010 at 08:04 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1