MS Excel VBA Code to Find the Date or Next Nearest Future Date

[OilEconomist]

Thanks in advance for your assistance. The following MS Excel VBA Function finds the nearest date to today (current date) from a list which will either be today, or some prior date or some future date. How would I modify the code to to eliminate it finding a prior date and only finding either the current date or the next nearest future date. Also, if the column is blank or has words, how to just skip that column?

Option Explicit

Sub DateNearestTest()

 'Dimensioning
    Dim j As Long
    
    Dim RowNoStart As Long
    
    Dim ColNo As Long
    Dim ColNoStart As Long
    Dim ColNoEnd As Long

    Dim ShtNmSrc As String
    Dim RowInputUse As String
    
    Dim LoopRng As Range
    Dim LoopCell As Range

    Dim DateMatchNearest As Variant


 'Code
    ShtNmSrc = "Important.Dates"

    RowNoStart = 8
    ColNoStart = 1
    ColNoEnd = 12

    With Sheets(ShtNmSrc)
     Set LoopRng = Range(Cells(RowNoStart, ColNoStart), Cells(RowNoStart, ColNoEnd))
     RowInputUse = "Yes"

     For Each LoopCell In LoopRng
        ColNo = LoopCell.Column
        'Function DateMatchNearestF(ShtNmSrc As String, RowNoStart As Long, RowInputUse As String, _
                            ColNoStart As Long, ColNoEnd As Long, ColInputUse As String) As Variant
        DateMatchNearest = DateMatchNearestF(ShtNmSrc, RowNoStart, RowInputUse, ColNo)
     Next LoopCell
    End With

End Sub


Function DateMatchNearestF(ShtNmSrc As String, RowNoStart As Long, RowInputUse As String, _
                            ColNo As Long) As Variant

 'Dimensioning
    Dim RowEnd As Long
    
    Dim iMaxDiff As Long
    Dim d As Long
    
    Dim b As Range
    Dim RngSrchDate As Range
    
    Dim fndDate As Variant

'_________________________________________________________________________________________________________
 'Code to find set the search range
    If RowInputUse = "Yes" Then
        With Sheets(ShtNmSrc)
         RowEnd = .Cells(Rows.Count, ColNo).End(xlUp).Row
        End With
    End If
    
    Set RngSrchDate = Sheets(ShtNmSrc).Range(Cells(RowNoStart, ColNo), Cells(RowEnd, ColNo))
    

 '_________________________________________________________________________________________________________
 'Code to find the date
    With Sheets(ShtNmSrc)
        With RngSrchDate
            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:=Sheets(ShtNmSrc).Cells(RowNoStart, ColNo), 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
            DateMatchNearestF = b.Value
        End With
    End With
    
End Function

 

[bebo021999]

With a such long code of sub and function, with no supporting image/screenshot of sample sheet, its so hard to imagin what you are trying to do.
All I can guess is:
Range of dates: A8:L8
Are you trying to search for a closest date in future?
For instant, D8 = 13-Apr, E8 = 14-Apr, the output should be 14-Apr.
Is that right?

 

[OilEconomist]

With a such long code of sub and function, with no supporting image/screenshot of sample sheet, its so hard to imagin what you are trying to do.
All I can guess is:
Range of dates: A8:L8
Are you trying to search for a closest date in future?
For instant, D8 = 13-Apr, E8 = 14-Apr, the output should be 14-Apr.
Is that right?View attachment 62353

Thanks @bebo021999 for your response. I took the following code and modified it: Find closest date to current date in VBA

Row 8 has the headings and the date for each section or in the columns starting in row 9 or row 9 could be blank or a certain column could have something else in it besides dates which means to skip that column.

I am trying to get the current date or the closest date in the future. You may ask what are you just matching the current date for? I am doing that because the heading represents an event.

The following image should clarify.

 

[bebo021999]

Within 6 columns of date, which column do you want to get the result, and what is it?