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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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
 

ru82

New Member
Joined
Aug 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I tested it on several tabs in the register. It appears to work perfectly. You're amazing! Thank you so much!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,709
Messages
5,637,914
Members
416,992
Latest member
lo_

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