Find a date in a range of dates [Solved]

Report
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
-
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
-
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 reply

Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
390
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
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
211
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.
TrowaD
Posts
2586
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 20, 2020
390
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.
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
211
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 :^(