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

#### svmac

##### Board Regular
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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### dk

##### MrExcel MVP
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
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
To follow up Dan, yes the code did work for me. Thanks again.

Stuart Macdonald

Replies
1
Views
179
Replies
0
Views
1K
Replies
2
Views
343
Replies
3
Views
662
Replies
7
Views
2K

Threads
1,181,530
Messages
5,930,426
Members
436,738
Latest member
JFry

### 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

### 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