none
VBA Search Date Function RRS feed

  • Question

  • I need a VBA function that will return the first date in a range of cells that is <= a user-specified date. The function must return a range object (not just the matched value, as in VLOOKUP) so that I can select the matching cell.

    Application.worksheetfunction.VLOOKUP works just fine for locating the value, but not the range.

    Thanks!

    Monday, December 12, 2011 4:38 PM

Answers


  • The Match worksheet function returns the row position within the specified range.
    That number can be used with the Cells property to return a range object.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    James Cone
    • Marked as answer by Marak60 Wednesday, December 14, 2011 3:39 AM
    Monday, December 12, 2011 5:21 PM
  • Jim,

    The solution I finally devised did make use of the MATCH function:

    Dim rngSearchRange As Range, rngLocation As Range
    Dim vntSearchDate As Variant

    vntSearchDate = Range("F2").Value  'User defined search date
    Set rngSearchRange = Range("B3:B26")  'Range of dates sorted ascending
    Set rngLocation = Application.Index(rngSearchRange, Application.Match(CLng(vntSearchDate), _
        rngSearchRange, 1))
    If Not (rngLocation Is Nothing) Then
      rngLocation.Select  'Highlight the date
    End If

    I have found that using these worksheet functions in VBA is a very quirky thing, i.e. "WorksheetFunction.Index" or "Application.WorksheetFunction.Index" did not work; it had to be "Application.Index"... what the hell is that all about?.  Also, had to use a Variant for the search date. A String or Date variable wouldn't work. And the Variant date had to be converted with CLng, or it wouldn't work.

    Bottom line: this solution works exactly as required, but it was a quirky mess to figure out.

    Thanks for all the responses!

    • Marked as answer by Marak60 Wednesday, December 14, 2011 3:39 AM
    Wednesday, December 14, 2011 3:38 AM

All replies


  • The Match worksheet function returns the row position within the specified range.
    That number can be used with the Cells property to return a range object.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    James Cone
    • Marked as answer by Marak60 Wednesday, December 14, 2011 3:39 AM
    Monday, December 12, 2011 5:21 PM
  • Say A1 thru A10 contain:

    12/12/2011
    12/11/2011
    12/10/2011
    12/9/2011
    12/8/2011
    12/7/2011
    12/6/2011
    12/5/2011
    12/4/2011
    12/3/2011

    and the target date is 12/8/2011

     

    The MAIN sub could be:

     

    Sub MAIN()
    Dim rList As Range, d As Date
    Dim r As Range
    Set rList = Range("A1:A10")
    d = DateSerial(2011, 12, 8)
    Set r = FindIt(rList, d)
    MsgBox (r.Address)
    End Sub

     

    and the function returning the range:

     

    Public Function FindIt(Tabl As Range, dd As Date) As Range
    Dim rr As Range

    For Each rr In Tabl
        If CDate(rr.Value) <= dd Then
            Set FindIt = rr
            Exit Function
        End If
    Next
    Set rr = Nothing
    End Function


    gsnu201111
    Monday, December 12, 2011 6:46 PM
    Moderator
  • On Mon, 12 Dec 2011 16:38:36 +0000, Marak60 wrote:
     
    >
    >
    >I need a VBA function that will return the first date in a range of cells that is <= a user-specified date. The function must return a range object (not just the matched value, as in VLOOKUP) so that I can select the matching cell.
    >
    >Application.worksheetfunction.VLOOKUP works just fine for locating the value, but not the range.
    >
    >Thanks!
     
    Are your dates sorted?  Ascending or Descending?
    When you write "the first date in a range of cells that is <= a user-specified date", do you meant the date that is closest to or equal to the user specified date; or do you mean the first date in the list that is less than or equal to the user specified date.
     
    IOW, with this list
    12/1
    12/2
    12/3
    12/4
     
    and a user specified date of 12/3,  do you want to return the address of 12/3 (the closest)  or 12/1 (the "first")
     

    Ron
    Monday, December 12, 2011 7:55 PM
  • The function must return a range object (not just the matched value, as in VLOOKUP) so that I can select the matching cell.

    Have you considered using AutoFilter and use a custom filter to simply display the dates that are <= to the required date; particularly since you have said you want to select the required cell. After turning off AutoFilter, the selected cell remains selected.

    To use custom filter in xl2007 (and I imagine 2010), turn on AutoFilter and after clicking the drop down arrow select Date filters and set the required filter.

    In earlier version of xl it is similar but I think it is called Custom filter.


    Regards, OssieMac
    Tuesday, December 13, 2011 3:03 AM
  • Jim,

    The solution I finally devised did make use of the MATCH function:

    Dim rngSearchRange As Range, rngLocation As Range
    Dim vntSearchDate As Variant

    vntSearchDate = Range("F2").Value  'User defined search date
    Set rngSearchRange = Range("B3:B26")  'Range of dates sorted ascending
    Set rngLocation = Application.Index(rngSearchRange, Application.Match(CLng(vntSearchDate), _
        rngSearchRange, 1))
    If Not (rngLocation Is Nothing) Then
      rngLocation.Select  'Highlight the date
    End If

    I have found that using these worksheet functions in VBA is a very quirky thing, i.e. "WorksheetFunction.Index" or "Application.WorksheetFunction.Index" did not work; it had to be "Application.Index"... what the hell is that all about?.  Also, had to use a Variant for the search date. A String or Date variable wouldn't work. And the Variant date had to be converted with CLng, or it wouldn't work.

    Bottom line: this solution works exactly as required, but it was a quirky mess to figure out.

    Thanks for all the responses!

    • Marked as answer by Marak60 Wednesday, December 14, 2011 3:39 AM
    Wednesday, December 14, 2011 3:38 AM
  • Ron,

    Thanks for your reply. Given a list of dates (single column), I want to search for a particular date and return the range containing all the dates that are >=  (correction to my last post - should be GREATER THAN or equal to) the specified search date. The dates are always sorted in ascending order.

    So, in your example, if the search date was 12/3, then I want to return the range containing 12/3 and 12/4. In the following example:

    12/1
    12/8
    12/15
    12/22
    12/29

    if the search values was 12/10, I'd want to return the range that contains 12/15, 12/22 and 12/29 (all dates >= 12/10).

    Which leads me to a flaw that I discovered in my solution. By passing "1" to the MATCH function as the Match_Type parameter, the range being returned starts with the GREATEST date that is <= the search date. So, in my last example, my method is returning all dates starting with 12/8.  Changing the Match_Type to "-1" seems to be what I'm looking for, but that requires that the entire date list be sorted in DESCENDING order.  I'm still looking for a good solution to this problem.

    -Mark

     

    Friday, January 20, 2012 7:24 PM
  • That is a very different question from what you originally proposed.  I'm not sure what you are going to do with it, but the following function, given a date, will return a range object that includes all of the dates >= that defined date. 

    Because it makes use of autofilter, it does require that your list of dates have a title. However, it does not require that the dates be sorted.

    It is written assuming that your dates are in column A; the title is in A1, and there is nothing below the list of dates.  There are, of course, other ways to select the range of dates ot process.

    Finally, since a worksheet function can only return a value, and not a range object, this function needs to be run within a VBA module.  So I have included a macro with an Input Box to select the earliest date.  You could also use a userform with a calendar control to accomplish the same thing, and present a "better" UI.

    It will produce an error if there are no dates in the included range.  I have chosen to have the output the range object of the label in that event.

    ==================================
    Option Explicit
    Sub GetDateRange()
    Dim dt As Date
        dt = InputBox("Earliest Date: ")
        Debug.Print DateRange(dt).Address
       
    End Sub
    '---------------------------------------------------------------
    Private Function DateRange(d1 As Date) As Range
        Dim rSrc As Range
        Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.AutoFilterMode = False

    'UNcomment the next line once you have this debugged
    'Application.ScreenUpdating = False

    With ws
        Set rSrc = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
        rSrc.AutoFilter field:=1, Criteria1:=">=" & Format(d1, "mm/dd/yyyy")
    End With

    On Error GoTo ErrExit
    Set DateRange = rSrc.Offset(rowoffset:=1). _
        Resize(rowsize:=rSrc.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

    ws.AutoFilterMode = False
    Application.ScreenUpdating = True
    Exit Function
    ErrExit: Set DateRange = rSrc(1)
    End Function
    =========================================

     


    Ron
    Monday, January 23, 2012 4:23 PM