• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba - extract month and compare

Malleshg24

New Member
Hi Team,

Compare Current month File which is range ("a2") to Range("b2") ,
b2 should be previous month file of range ("A2), Thanks in advance for your help.



AB
Current Month FilePrevious Month File
ABC XYZ INDIA PRIVATE LIMITED (Period = May-2020 - May-2020)ABC XYZ INDIA PRIVATE LIMITED (Period = Apr-2020 - Apr-2020)

>>> use code - tags <<<
Code:
Sub TEST()

Dim CurrentMonth As String
Dim PreviousMonth As String

CurrentMonth = Split(Range("a2"))(7)
PreviousMonth = Split(Range("b2"))(7)

MsgBox CurrentMonth
MsgBox PreviousMonth


'output - CurrentMonth = May-2020   compare here previous month should be Apr - 2020
'output - PreviousMonth  = Apr-2020 


End Sub
 
Last edited by a moderator:
Looks right so far—that is, I agree that the output should be as you noted. So you now have two strings, one saying "May-2020" and the other "Apr-2020". What do you want to accomplish with them?
 
Hi Bob,

if range b2 Contain Feb-2020,or Mar -2020, then Macro should give error message,
Immediate Previous month not available.

sorry for cross posting, as I was looking for urgent help on this.



Thanks
mg
 
Malleshg24
Hint: If You follow Forum Rules as those are written then You would get quicker answers.
You should refresh You memory and reread those as soon as possible.
There are many good hints for You too.
 
So you have two strings, both in the format "mmm-yyyy", and you want to be sure that PreviousMonth is exactly one month earlier than CurrentMonth.

To do this, I would find a way to convert both of them to Excel dates and then compare them. Here's one way...

Wait—I was going to develop a little routine to evaluate the two strings, convert them to Excel dates, then calculate the number of months between them. But I just noticed there are two functions that'll do all this work for you.

DateValue(string) looks at a character string and attempts to convert it to a date value. I thought you would have to add a bogus day value to the string to make DateValue work, like this:
Code:
CurrentDay = DateValue("01-" & CurrentMonth) 'DateValue("01-Jun-2020")
But it's not necessary; DateValue(CurrentMonth) works just fine.

Then there's another VBA function called DateDiff, like this:
Code:
Interval = DateDiff("m", PreviousDate, CurrentDate)
That calculates the number of months (if the first argument is "m") between two dates. If the 2nd argument is one month less than the 3rd argument, the result placed in Interval is 1; if you reverse the dates, Interval becomes -1. Write it either way you want, and then check whether the difference is the proper interval.

So you can write it like this:
Code:
If DateDiff("m", DateValue(PreviousMonth), DateValue(CurrentMonth)) <> 1 Then 'error condition
 
Back
Top