excel - Find closest date to current date in VBA

Question

I am trying to create a macro that activates the first cell that is the current date or closest to it in a column.

I tried this:

Cells.Find(What:=Date, After:=Range("B6"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

Which works only if the current date is somewhere in the column, but if it is not then I get an error. How do I modify it to keep searching until it find the closest date to the current date?

Answer 1

Rather than looping through a progressive series of additions and subtractions to the current date, a short sub section of your procedure can determine the date that you are looking for.

Sub nearest_date()
    Dim b As Range, lr As Long, iMaxDiff As Long, d As Long, fndDate

    With ActiveSheet  'set this worksheet properly!
        With .Range(.Cells(6, 2), .Cells(Rows.Count, 2).End(xlUp))
            iMaxDiff = Application.Min(Abs(Application.Max(.Cells) - Date), Abs(Date - Application.Min(.Cells)))
            For d = 0 To iMaxDiff
                If CBool(Application.CountIf(.Cells, Date + d)) Then
                    fndDate = Date + d
                    Exit For
                ElseIf CBool(Application.CountIf(.Cells, Date - d)) Then
                    fndDate = Date - d
                    Exit For
                End If
            Next d
            Set b = .Find(What:=fndDate, After:=Range("B6"), LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)

            'do something with the closest date. I do NOT recommend using .Select for anything beyond demonstration purposes
            b.Select
        End With
    End With
End Sub

This is a passive way to determine the closest date. Once found, we know it is there and questionable coding practices like On Error Resume Next can be avoided. TBH, once you know is it there, an application.Match could locate it just as easily.