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?

    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