Find a date in a range of dates [Solved]

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.

See more 

2 replies

Posts
2535
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2019
368
0
Thank you
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
202 -
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
2535
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2019
368 -
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
202 -
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 :^(
Respond to TrowaD
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463
0
Thank you
Considering a 100% pay raise at ccm really means having to navigate even more useless reviews on obsolete software! No thanks!
Respond to ac3mark