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?

    Option Explicit
    Public Sub TestMe()
        Dim dateRanges As Range    Set dateRanges = Range("D1:D11")
        Dim mn As Variant    With Application        
        mn = .Match(.Min(dateRanges), dateRanges, 0)    End With
        MsgBox Range("E" & mn).Value2
    End Sub

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!