Searching a range of dates for a match, then perform action

[svmac]

I currently use a listbox on a userform to perform a series of actions within a spreadsheet, but it is proving to be a bit clumsy.

What I would like to do is have a macro search for a date within the range, based on the value of a cell - the problem is that the range of dates does not include all dates, and is entirely random. A user may enter 01-Jul-01 in cell J7, but the range "Dates" does not contain this date - I would want it to search for the next date after this one - 24-Jul-01.

Thnx in advance
Stuart Macdonald

 

[dk]

Hello,

If I understand your problem correctly you should be able to use something like this. It takes the value in J7 and then searches the range Dates for the earliest date falling after that found in J7.

Let me know how you get on. This code will work fine unless the range of dates is very large. If that is the case then this code could be improved (but would be more complicated).

Sub GetNextDate()
Dim rngeCell As Range
Dim dteSearchDate As Date
Dim rngeNext As Range
Dim dteNextDate As Date

dteSearchDate = Range("J7").Value

dteNextDate = #1/1/2099#
For Each rngeCell In Range("Dates").Cells
    If rngeCell.Value < dteNextDate And rngeCell.Value > dteSearchDate Then
        Set rngeNext = rngeCell
        dteNextDate = rngeCell.Value
    End If
Next

If rngeNext Is Nothing Then
    MsgBox "No date later than " & dteSearchDate & " was found."
Else
MsgBox "The next date after " & dteSearchDate & " is " & rngeNext.Value & _
    " located in cell " & rngeNext.Address
End If
End Sub

 

[svmac]

Dan, thanks for the reply.

I will give the code a whirl - looks like what I am after - and let you know how I get on.

Many thanks
Stuart Macdonald

 

[svmac]

To follow up Dan, yes the code did work for me. Thanks again.

Stuart Macdonald