Macro to find today’s date in list

[Catsonheat]

Hi all, I have a list of dates in Column C for every day of the year. They are listed as:

C5: Monday, 1 January
C6: Tuesday, 2 January

Etc.

I am trying to get a macro that finds today’s date using the Today() function so I can always skip to the date at the time rather than scrolling down through the list.

Any ideas? Thanks in advance!

[PFDave]

This code will do what you're asking, but I have one concern with your data and that is how it's entered in each cell.

Sub PFDave()

Dim c As Range, ws As Worksheet
Set ws = Sheets("Sheet1")

For Each c In ws.Range(ws.Cells(1, 3), ws.Cells(ws.UsedRange.Rows.Count, 3))
    If c = Date Then
    c.Select
    Exit Sub
    End If
Next

End Sub

[Catsonheat]

Hi PFDave, that worked a treat! Thanks! Sorry to be a pain but would it be possible to explain how that macro is working? Just so I can understand it for the future and if I need to adapt it I can learn for myself a bit better!

[PFDave]

Of course I can

declare variable names and their type:

 Dim c As Range, ws As Worksheet

Set variable for ws

Set ws = Sheets("Sheet1")

This creates the the loop which we are using For Each in a certain range. the ws.Range part is setting the range which we could do prior by declaring and setting another variable. N.B - We're using UsedRange to find the last row we want to use

 For Each c In ws.Range(ws.Cells(1, 3), ws.Cells(ws.UsedRange.Rows.Count, 3))

To declare and set another variable for the range we would

 Dim rng as range
Set rng = ws.Range(ws.Cells(1, 3), ws.Cells(ws.UsedRange.Rows.Count, 3))

This would then change the For Loop to

For Each c In rng

Next we're checking if the cell (c) is = to todays date and if it is we want to select it and exit this routine because we've found what we're looking for. If it's not equal to todays date it will move onto the next cell in the range

If c = Date Then
    c.Select
    Exit Sub
    End If

[Catsonheat]

Awesome thanks Dave! Will mark as solved when I’m not on my phone as I can’t seem to find the option! Really appreciate your help!

[PFDave]

Glad it's sorted mate, any feedback greatly received