Match reference # and return most recent by date

  • I have a list of reference #s that I am matching to 2 data sets and if a match is found, the macro will pull certain fields over.


    For the 1st database I don't have multiple matches by reference # but in the second database I have multiple matches by reference # and I would like to pull only the most recent entries (preferably 2 or 3).


    The code right now will put the data in specific columns; for database 2 in Column H,I,J,K. In case there are up to 3 matches by reference # I would like the code to continue and add the second match in Column L,M,N,O and so on for the third.


    The code is below. While I was searching I was wondering if this code might help sorting by date?


    Code
    1. Option Explicit
    2. Public Sub TestMe()
    3. Dim dateRanges As Range Set dateRanges = Range("D1:D11")
    4. Dim mn As Variant With Application
    5. mn = .Match(.Min(dateRanges), dateRanges, 0) End With
    6. MsgBox Range("E" & mn).Value2
    7. End Sub


    this is the code i have right that is working for simple match