Finding closest date

[Exceldud]

I have a spreadsheet which has thousands of rows with new rows getting added daily. Using VBA how can I go directly to the cell in column D which has the date closest to the today's date. Basically I want to prevent the user from scrolling through thousands of records to get to the records that are closer to today's date. The vba should run at the time the file is opened and focus immediately goes to the record closer to today's date.

I had found the below code online but it does not seem to work.

Sub Today_Date()
Closest = Evaluate("=MATCH(MIN(ABS(TODAY()-D2:D10000)),ABS(TODAY()-D2:D10000),0)")
Range("D2:D10000").Cells(, Closest).Select
End Sub

 

[Peter_SSs]

Try this variation in the ThisWorkbook module of your workbook

I have assumed that your workbook may have more than one worksheet but the worksheet you want this to happen on is called "Sheet1"

Private Sub Workbook_Open()
  Dim closest As Long
  
  Sheets("Sheet1").Activate
  closest = Evaluate("=MATCH(MIN(ABS(TODAY()-D2:D10000)),ABS(TODAY()-D2:D10000),0)")
  Range("D2:D10000").Cells(closest).Select
End Sub

 

[Exceldud]

Thanks Peter. Worked like a charm.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.