Excel VBA - Need code to return the most frequently occurring month (number) in a column 2

[renigar]

I have a column of dates in a worksheet and need to get the most frequently occurring month returned as a number (April would be 4) in a variable. I found the following piece of code but it returns the most frequently occurring date. I've tried a couple of iterations but I'm not having any luck. Any suggestions?

MostMonth = Application.Evaluate("INDEX(B1:B" & LastRow & ",MODE(IF(B1:B" & LastRow & "<>"""",MATCH(B1:B" & LastRow & ",B1:B" & LastRow & ",0))))")

Thanks,
renigar

 

[SkipVought]

Hi,

You don't really need VBA, if you were to make a Structured Table. If you're not using the Structured Table feature, introduced with Excel 2007, you are short changing yourself!

Here's my table named tDAT...
[pre]
My Dates
2/1/2016
1/2/2016
2/3/2016
3/1/2016
2/2/2016
1/1/2016
[/pre]

Here's my forumla, entered as an Array Formula (ctr+SHIFT+ENTER)
[tt]
=INDEX(MONTH(tDAT[My Dates]),MODE(IF(MONTH(tDAT[My Dates])<>"""",MATCH(MONTH(tDAT[My Dates]),MONTH(tDAT[My Dates]),0))))
[/tt]

And the result for this data is 2

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

For continuous range of dates:

MsgBox Application.Evaluate("MODE(MONTH(B1:B" & LastRow & "))")

or excel formula:
[tt]=MODE(MONTH(B1:B8)[/tt])

combo

 

[SkipVought]

Nice, combo! I've never used MODE().

On the sheet...
[tt]
=MODE(MONTH(tDAT[My Dates]))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

Thanks Combo, I had known about the mode formula on the sheet, just wasn't sure how to VBA it. Much easier than I thought. Skip, I've learned the value of structured tables from the last question you helped me with. In this case I needed to get the mode value into a variable for a message box for the user to make a choice. Thanks.

 

[SkipVought]

FYI, I view Excel a lot different than I would an Access app where the user ought to be isolated from direct the interaction with the database.

I like to keep the user on the sheet, by putting the controls on the sheet, rather than isolating the user from the data on the sheet via a userforms.

Just another point of view.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So here's the Structured Table/VBA solution...

MsgBox Application.Evaluate("Mode(Month(tDAT[My Dates]))")

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

I appreciate the tip. Thanks Skip