Macro to Select Todays Month

[Rohmeo]

Hi,

A simple one Im sure for the wizards on this forum.

Would someone provide an idea to create a macro to select todays month. Details are below:

• Column A has a list of multipe records by month (see Exhibit 1)
• I would like to create a macro that goes to today's month (i.e top of 'Jan 2018')
• Cells are in Column A with number format mmm yyyy
• I have a similar macro for selecting today date - but would like to amend to select todays' month (see Exhibit 2)

Thanks
Rohmeo

Exhibit 1

Nov 2017
Nov 2017
Dec 2017
Dec 2017
Dec 2017
Dec 2017
Dec 2017
Dec 2017
Jan 2018
Jan 2018
Jan 2018


Exhibit 2

Sub SelectToday()

For Each cell In ActiveSheet.Range("A1:A500")
If cell.Value = [Today()] Then
cell.Select
End If
Next

End Sub

 

[Joe4]

Welcome to the Board!

Cells are in Column A with number format mmm yyyy

Whether they show it or not, all valid dates in Excel are stored with a year, month, and day component.
So while you may not be showing the day, there really is a day associated with those dates.
The question is, is it always the first day of the month, or might it be other days?
A simple way to check is to temporarily change the format on the column to a date format that shows days, and see what it shows.
Your answer may shape how we write the code.

Note: If changing the date format on your column does NOT change what those entries look like, then we are actually dealing with Text and not Dates (formatting is meaningless on Text).
That would mean a whole different approach to the problem.

 

[MARK858]

Just as separate point in the code you posted rather than evaluating a formula i.e.

If cell.Value = [Today()] Then

just use Date i.e .

If cell.Value = Date Then

 

[Rohmeo]

Thanks for the quick reply!

Ok, that makes sense. As an example changing the cells to "dd mmm yyyy" format changes the dates to a 01 Jan 2018". So it seems they are always the first day of the month.

Thanks
Roh

 

[Rohmeo]

Thanks @MARK858. Unfortunately, the "If cell.Value = Date Then" replacement doesn't results in any selection.

 

[MARK858]

Hmmm, Date is the exact VBA equivalent of the formula

=Today()

 

[Joe4]

Try this:

Sub SelectCurrentMonth()
    
    Dim myDate As Date
    
'   Get the first day of the current month
    myDate = Date - Day(Date) + 1
    
'   Go to current date
    On Error GoTo not_found
    Columns("A:A").Find(What:=myDate, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    
    Exit Sub
    

not_found:
    MsgBox "Current month not found!", vbOKOnly, "ERROR!"

End Sub

 

[Rohmeo]

Wow. That worked... thanks!
I know you put some comments in there, could you provide some more detail regarding how it works so I can keep it in mind for next time?

Thanks

 

[Joe4]

Sure. The first part is just finding the first day of the current month. The logic is pretty simple. Start with the current date, subtract the current day, and then add one.
So for today, it would be:
1/19/2018 + 19 - 1 = 1/1/2018

The second part is just finding that date in column A. This code can be gotten pretty easily by turning on the Macro Recorder, and record yourself selecting column A and then finding 1/1/18 using Excel's Find functionality (the Macro Recorder is a great tool for getting little snippets of code like this!).

And then I just put some error handling in there to tell it what to do if it does not find the date we are looking for.
Here is a good write-up on Error Handling: http://www.cpearson.com/excel/errorhandling.htm