Macro to find last instance of today's date

[maps5]

Hello.

In my workbook, column A contains dates, which can be repeated.
The table is not sorted (and can't be) on column A.

I need a macro that will find the last instance of today's date, and select the cell (or table row) immediately under.


I haven't been able to find a solution for this, as all I could find only works if there is only 1 occurance of today's date (as in calendars).

Hope someone can help me on the way here.

Thank you in advance.
maps

 

[Sektor]

Change date to the one you need

Sub FindLastDate()
    Dim dtSearch As Date
    Dim cell As Range
    dtSearch = DateTime.DateSerial(2018, 5, 6)
    Set cell = Columns("A:A").Find(What:=dtSearch, SearchDirection:=xlPrevious, LookAt:=xlWhole)
    If Not cell Is Nothing Then
        cell.Offset(1).EntireRow.Select
    End If
End Sub

 

[maps5]

Change date to the one you need

Sub FindLastDate()
    Dim dtSearch As Date
    Dim cell As Range
    dtSearch = DateTime.DateSerial(2018, 5, 6)
    Set cell = Columns("A:A").Find(What:=dtSearch, SearchDirection:=xlPrevious, LookAt:=xlWhole)
    If Not cell Is Nothing Then
        cell.Offset(1).EntireRow.Select
    End If
End Sub

Thank you, Sektor!

This worked perfectly.

I've addapted it to always look for today's date.

Since I tried it on a table without a record/row with today's date, no row was selected... (as expected)
But this brings me to my next request.

Can I put this code in a loop, so that if today's date isn't found, it'll look for yesterday, and select the row below the last instance of yesterday's date, if found...
The loop should repeat until it finds a date within the past 7 days... if nothing is found, it should stop searching, meaning that nothing will be selected...

Thank you once again for the help.

 

[Fluff]

How about

Sub FindLastDate()
    Dim dtSearch As Date
    Dim cell As Range
    Dim i As Long
    For i = 0 To 6
      dtSearch = Date - i
      Set cell = Columns("A:A").Find(What:=dtSearch, SearchDirection:=xlPrevious, LookAt:=xlWhole)
      If Not cell Is Nothing Then
          cell.Offset(1).EntireRow.Select
          Exit For
      End If
    Next i
End Sub

 

[maps5]

How about

Sub FindLastDate()
    Dim dtSearch As Date
    Dim cell As Range
    Dim i As Long
    For i = 0 To 6
      dtSearch = Date - i
      Set cell = Columns("A:A").Find(What:=dtSearch, SearchDirection:=xlPrevious, LookAt:=xlWhole)
      If Not cell Is Nothing Then
          cell.Offset(1).EntireRow.Select
          Exit For
      End If
    Next i
End Sub

Thank you very much, Fluff!
This seems to be working wonderfully.

In your code, I don't seem to see where the date is being set to today's date...
Do you mind explaining how the code knows the first date that it needs to search for?

Thank you once again.
Regards,
maps

 

[Fluff]

The line in blue

For i = 0 To 6
      [COLOR=#0000ff]dtSearch = Date - i[/COLOR]

sets the date, so on the first loop i =0 so dtSearch will be today's date, on the second pass i=1 so date -1 will be yesterday, etc

 

[maps5]

The line in blue

For i = 0 To 6
      [COLOR=#0000ff]dtSearch = Date - i[/COLOR]

sets the date, so on the first loop i =0 so dtSearch will be today's date, on the second pass i=1 so date -1 will be yesterday, etc

ah, yes... I got it now...

Another thing...
The code will select the entire row... how can I tweak it so only the table row is selected?

I tried the following right after the row is selected, and after the For cycle ran it's course (just below Next i), but to no avail...

Range(Selection, Selection.End(xlToLeft)).Select

 

[Fluff]

Try

cell.offset(1).resize(,[COLOR=#ff0000]10[/COLOR]).select

where the value in red needs to be changed to the number of columns.

 

[maps5]

Try

cell.offset(1).resize(,[COLOR=#ff0000]10[/COLOR]).select

where the value in red needs to be changed to the number of columns.

Hello Fluff.

The code worked as expected...
But for my case it only almost worked...

The problem is that the date I'm searching for is in column K, which means that this line of code is selecting a given row from column K and 10 columns forward...
for instance, the selection is resized from the entire row to become K19:T19...
And, based on my trials, I can't input a negative number as an argument for the resize...

How can I offset the selection to start in column A and x columns forward (10 in your example), instead of starting in column K, which is the column where it looks for the date?

Thank you in advance.



EDIT:

I seem to have fixed it with the following line...

Selection.Offset(0, -10).Select

Is there a more 'elegant' way of doing this?

Thanks.

 

[Fluff]

There are various ways of doing it & here are 3 options

cell.Offset(, -10).Resize(, 10).Select
Range("A" & cell.Row).Resize(, 10).Select
Intersect(cell.EntireRow, Range("A:J")).Select

All give the same result, which one you use is mainly personal preference.