none
VBA Vlookup won't match dates RRS feed

  • Question

  • Hi all,

    First post here and I'm hoping someone can help me with a problem I'm having with Vlookup and dates. My knowledge of VBA is quite limited, so I'm hoping this is a quick fix.

    So, I have a spreadsheet with 2 named ranges "Source" and "Compare", which are one and two columns wide. I have dates in the first range, which I'm trying to match to the dates in the second range. I have written a short VBA Vlookup code to compare these dates in an array (the spreadsheet has 18,000 rows of data so I thought using arrays would be the quickest way to do the vlookup), but it won't match any dates. I've tested the code using numbers and text instead of dates, but when I put the dates back in it just won't work. The code should then paste the array into another named range on my spreadsheet ("Match").

    I've tried everything I can think of - checking date formats are the same, checking for any spaces, converting the dates to a number to see if they are the same, using a true / false check in the spreadsheet to see if the cells are the same.

    The dates in the first range were originally formatted as "custom" but I changed these to date before running the macro. My VBA code is as follows:

    Sub lookup()
    Dim MyArray()
    x = Range("Source").Value
    y = Range("Compare").Value
    RowNumbers = 18000
    ReDim MyArray(1 To RowNumbers)
    For r = 1 To RowNumbers
    MyArray(r) = Application.VLookup(x(r, 1), y, 2, False)
    Next r
    Range("Match").Value = Application.WorksheetFunction.Transpose(MyArray)
    End Sub

    Any help would be greatly appreciated, thanks.

    Thursday, October 2, 2014 7:52 PM

Answers

  • Possibly because the Excel worksheet function gets confused with the array of VBA dates which are always US-centric.

    As suggested convert the dates to intrinsic values which you can do simply with the Value2 property, eg

    x = Range("Source").Value2

    Alternatively pass the actual ranges containing the values

    Dim x as Range
    Set x = Range("Source")

    Looks like you are dealing with a large ranges, experiment both ways. With a large set it might be quicker to avoid the worksheet function and roll your own, particularly if sorted in which case search and look into searching the array with a "binary chop"

    In passing, no need to do that Transpose, write to a 2D arrray

    ReDim MyArray(1 To RowNumbers, 1 To 1)

    MyArray(r, 1) = xxx
    and finally simply
    Range("Match").Value = MyArray

    • Marked as answer by Hans Wilsdorf Saturday, October 4, 2014 12:19 AM
    Friday, October 3, 2014 8:56 PM
    Moderator

All replies

  • Re:  no match on dates w Vlookup

    Try pasting both arrays on another sheet and take a quick look at them.
    Is the entry in the formula bar identical for "identical" items?

    You might want to change  both ranges to numbers, do the match and then convert the result back to dates.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Edited by James Cone Monday, October 31, 2016 7:01 PM
    Thursday, October 2, 2014 10:18 PM
  • Thankyou I'll give that a try. If I convert the dates to numbers would it be quicker to do that on the spreadsheet or once the dates are in the array?
    Thursday, October 2, 2014 10:43 PM
  • Re:  dates to numbers

    You should be able to change the the entire range cell from Dates to Numbers using the normal cell formatting utility.
    I would make sure to use just enough decimal places to ensure the comparison you require.
    If you want to find "04/01/2014" in both lists, then you want whole numbers.
    If  "04/01/2014 12:48:40 PM" is essential to the comparison then you will need sufficient decimal places.
    '---
    Jim Cone
    Thursday, October 2, 2014 11:21 PM
  • Would you mind sharing the worksheet with us? Or make a dummy that allows us to reproduce the problem please.

    Friday, October 3, 2014 7:39 AM
  • Thank you for your kind help, and also to Jim. Converting dates to numbers works, but I still don't understand why the vlookup won't work using the actual dates. I'm sure it must be something to do with the formatting of the data but I just can't see what. I have created a dummy worksheet but can't see how I can upload this.
    Friday, October 3, 2014 3:53 PM
  • Possibly because the Excel worksheet function gets confused with the array of VBA dates which are always US-centric.

    As suggested convert the dates to intrinsic values which you can do simply with the Value2 property, eg

    x = Range("Source").Value2

    Alternatively pass the actual ranges containing the values

    Dim x as Range
    Set x = Range("Source")

    Looks like you are dealing with a large ranges, experiment both ways. With a large set it might be quicker to avoid the worksheet function and roll your own, particularly if sorted in which case search and look into searching the array with a "binary chop"

    In passing, no need to do that Transpose, write to a 2D arrray

    ReDim MyArray(1 To RowNumbers, 1 To 1)

    MyArray(r, 1) = xxx
    and finally simply
    Range("Match").Value = MyArray

    • Marked as answer by Hans Wilsdorf Saturday, October 4, 2014 12:19 AM
    Friday, October 3, 2014 8:56 PM
    Moderator
  • Thank you so much, this has been a massive help. Both solutions you suggested have worked, but using the set x = range solution has sped my macro up enormously (I've used this in other parts of my code). My macro now runs in less than 2 minutes compared to 8 previously. Thanks again, I really am very grateful.
    Saturday, October 4, 2014 12:24 AM
  • Glad things have improved but 2 minutes seems a long time. If say you have a lookup table of 18000 rows, and looping 18000 lookups, something under a couple of seconds should be possible, even if that means sorting the table elsewhere and rolling your own lookup function.
    Saturday, October 4, 2014 2:24 PM
    Moderator