go to cell in column with date closest to today's date

[ru82]

I am trying to create a macro that I can click on so that wherever I am in a worksheet, it will zip me over to the cell in column A that has the date closest to today's date.

I set it up initially by recording a macro and searching for a date I already knew was in the column as a starter.

Then I tried to add a DIM based on other posts I found on this forum/google search:

Sub FindToday()

  Dim myDate As Date
  myDate = Date
'
    Columns("A:A").Select
    Selection.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
End Sub

All I did was add the two definition lines at the top and change the specific date "8/13/2020" to the variable todayish for the What.

This gets me an error:

Run-time error '91':
Object variable or With block variable not set

I'm not sure what I'm doing wrong?

Any help would be appreciated.

 

[jasonb75]

When you use Find in vba it is looking for the exact date, not closest match.

A few questions to establish the most practical method.

Are the dates sorted? If yes, is it oldest or newest date at the top?

What do you class as closest date? Do you want closest date on or before today, on or after today or just the nearest either way? If either way then what if there is a tie? For example, if the list contains yesterday and tomorrow but not today, which would be correct?

 

[ru82]

The dates are mostly in chronological order, oldest at the top newest at the bottom. But occasionally lines get inserted out of order as multiple people are in it and not everyone is consistent about moving whole lines instead of just editing a date if things change. To further complicate things, there's pivot tables analyzing the data positioned to the right of the register table, so we can't sort the lines - So if it's possible to account for the dates being out of order that would be for the best, I think.

Closest date on or before today would be ideal. Favoring that even in the event of a tie.

I appreciate your time.

 

[jasonb75]

I've only done a quick test but this appears to do what you need

Sub FindToday()
Dim myDate As Date, rFound As Range
    myDate = Evaluate("MAXIFS(A:A,A:A,""<=""&TODAY())")
    Set rFound = Range("A:A").Find(What:=myDate, After:=Range("A1"), LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not rFound Is Nothing Then Application.Goto rFound
End Sub

 

[ru82]

I tested it on several tabs in the register. It appears to work perfectly. You're amazing! Thank you so much!

 

[jasonb75]

You're welcome

Just so you know, the last line in the code acts as an error trap. This means that if no valid date is found in column A that the code will do nothing instead of producing a runtime error.