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

ru82

New Member
Joined
Aug 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
I've only done a quick test but this appears to do what you need
VBA Code:
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
 
Upvote 0
I tested it on several tabs in the register. It appears to work perfectly. You're amazing! Thank you so much!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top