Find a date in a range of datesSolved/Closed

Question

Hi All.

Another silly Macro question that I cant seem to find an answer for ...

As my wife does silly shift at work I have developed an excel spreadsheet that has all of the dates of the year listed and it highlights the days she is working. This is a bit of data that you might not need , but I include it just in case ... the calendar part is in a 3 week grid as she has a 3 week rota.

To get the correct year my other half inputs the year into cell A1 and the magic formula in cell C3 brings in the day of the week closest to the start of the year (this year it brings in 30 Dec). The formula in cell C3 is
=DATE($A$1,1,1)-WEEKDAY(DATE($A$1,1,1),1)+1
The rest of the dates  (going downwards on the spreadsheet) are generated  with the formulas =C3+1 and =C4+1 and =C5+1 and so on.  On cell D3 the formula is =C23+1 and subsequent downwards entries are =D4+1 and =D5+1 and ... until all the year is present.

Now to the problem. Suppose we get invited to a party in October (you know I like parties) then she has to scroll through loads of data to find the date required to see if she is working or not. To speed up the search I have set up a few MACRO buttons with the names of the month on. The idea is to press a button and the cell containing the first of the month is selected and brought into view. How can I do this? I cant even use the "find and replace" tool as the cell doesn't actually contain the text "October".

Internet searches have not given me even the faintest clue on how to do this so I have no code to show you what I have tried.

Any help would be appreciated.

TrowaD · Answer

Hi BrianGreen,

Can you check if the following code performs as requested (to find the first day of Januari):
Sub RunMe()
Dim mDate As Date

mDate = "1-1-" & Range("A1")
For Each cell In Range("C3:T23")
    If cell.Value = mDate Then
        cell.Select
        Exit Sub
    End If
Next cell
End Sub

When I entered the dates as you described, the range used was C3:T23.
The date format in my country is day-month-year. You might want to change code line 4 to match your date format.

For Februari code I would have to change the 4th code line to mDate = "1-2-" & Range("A1"). But again this will probably be different for you because of a different date format.

Hopefully this helps you out.

Best regards,
Trowa

Find a date in a range of dates

1 response

Similar discussions

Subscribe To Our Newsletter!