Find next date from todays date

[ttratl]

Hi Everyone,

I have part of a worksheet filled with unsorted dates (dd/mm/yyyy). Specifically the area is AA6:AJ200.

I need a macro that will find the next date (from today).

I've tried some code I use in another workbook, but that only searches one sorted column.

Any ideas would be great...

 

[Comfy]

What would you want the outcome to be? The actual next date or the cell address?

 

[Trevor G]

Try something like this

Sub find1()
Range("A1").Select
Do Until ActiveCell.Value = Date + 1
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Comfy]

http://www.rondebruin.nl/find.htm

Modified slightly

Sub Find_Next_Date()
    Dim FindString As Date
    Dim Rng As Range
    Dim i As Long
    i = 1
    
    With Sheets("Sheet1").Range("AA:AJ")
    Do Until i = 366
    FindString = CLng(Date + i)
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
            Exit Sub
        Else
            i = i + 1
        End If
    Loop
    End With
End Sub

This will continue until it finds a match or there is no date within the Next Year

 

[Roger Govier]

Hi

I have defines a range called myDates as A6:J28 - change to suit your range.

This code colours any cells containing the date being searched for, but you can amend that for whatever you need to do when a date is found.

I made cell A1 hold the search date, with a formula of =TODAY()+1, but you could place that in the code if you wanted.

Sub FindNextDate()
    Dim R As Range, Found As String
    Dim searchDate As Date

    Range("myDates").Interior.ColorIndex = xlNone
    searchDate = Range("A1").Value
    With Range("mydates")
        Set R = .Find(searchDate, LookIn:=xlValues)
        If Not R Is Nothing Then
            Found = R.Address
            Do
                R.Interior.ColorIndex = 15
                Set R = .FindNext(R)
            Loop While Not R Is Nothing And R.Address <> Found
        End If
    End With
End Sub

 

[jasonb75]

If you just want the date then maybe

Dim nextdate As Date
    nextdate = Evaluate("MIN(IF(AA6:AJ200>TODAY(),AA6:AJ200))")
If nextdate <= Date Then
    MsgBox "No next date found"
Else
    ' do something with nextdate
End If

 

[ttratl]

Thanks Comfy,

Just need the cell to be selected.

Thanks Trevor G,
Can't get that to work. If I change Range("A1").Select to any of the columns where my dates are it just loops continually and doesn't find any dates.

 

[jasonb75]

Slight modification to my previous suggestion

Dim nextdate As Date
    nextdate = Evaluate("MIN(IF(AA6:AJ200>TODAY(),AA6:AJ200))")
If nextdate <= Date Then
    MsgBox "No next date found"
Else
    [AA6:AJ200].Find(What:=nextdate, LookIn:=xlValues, LookAt:=xlWhole).Select
End If

 

[ttratl]

Wow - thanks guys.

Comfy's code works for me. I'll check out Ron's page too.

I didn't try Roger's code as I have 4 conditional format rules colouring lots of cells and it took a while to get that to work - so I didn't want to mess that up.

Thanks everyone! 3 hours research - then come to Mr Excel and get a solution in minutes!
Love this site!

 

[Roger Govier]

hi

If you don't want to colour the cell then use

Sub FindNextDate()
    Dim R As Range, Found As String
    Dim searchDate As Date

    Range("myDates").Interior.ColorIndex = xlNone
    searchDate = Range("A1").Value
    With Range("mydates")
        Set R = .Find(searchDate, LookIn:=xlValues)
        If Not R Is Nothing Then
            Found = R.Address
            Do
                Msgbox ("Cell found at " & r.address)
                Set R = .FindNext(R)
            Loop While Not R Is Nothing And R.Address <> Found
        End If
    End With
End Sub