Find a date in a range of dates

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Updated on Sep 24, 2019 at 12:00 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Oct 2, 2019 at 09:24 AM
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.

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 19, 2019 at 12:29 PM
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
1
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Sep 20, 2019 at 08:56 AM
Thank you so much Trowa - That works perfectly.

I would never have got that in a thousand years. Now I just need to figure out how it works to deepen my understanding. Ill scour the net and see what I can find regarding the "& Range()" in the "mDate = "1-2-" & Range("A1")" line (line 4 in your code).

As I cant award you a 100% CCM pay rise I would like to offer you the use of my (virtual) private jet whenever you need to get away from it all. Either that or all the virtual beer you can drink!

Thanks again.

Brian.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 23, 2019 at 11:53 AM
Thanks Brian, unfortunately I've already crashed the jet. Dang, I should really have gone for the beer ;).

Range("A1") stands for the location on your sheet where you stored the year, so you don't have to change the code next year.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Oct 2, 2019 at 09:24 AM
Of cause ... a senior moment there - it was the concatination that was confusing me - somehow I thought it was talking about an array.

Funny how I'm getting more of these senior moments :^(
0