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

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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).

Code:
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

Board Regular
Joined
Feb 16, 2002
Messages
182
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

Board Regular
Joined
Feb 16, 2002
Messages
182
To follow up Dan, yes the code did work for me. Thanks again.

Stuart Macdonald
 

Forum statistics

Threads
1,144,765
Messages
5,726,164
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top