Macro to Select Todays Month

Rohmeo

New Member
Joined
Jan 18, 2018
Messages
6
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
Just as separate point in the code you posted rather than evaluating a formula i.e.
Code:
If cell.Value = [Today()] Then
just use Date i.e .
Code:
If cell.Value = Date Then
 
Upvote 0
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
 
Upvote 0
Try this:
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top