Check date versus variables

[Turbosnor]

Hi Guys,

I’m a newby at excel VBA programming and struggling with handling dates.
In my sheet cell B2 has a date which is, for example, 1/1/2018 but it shows as jan/18 because of formatting reasons.
I have 2 variables: MyYear which has the year and MyMonth which has the month (first 3 characters of the month name). Btw these variables are not necessary the current date!
I’m struggling how to validate that combination of variables MyMonth/MyYear to the content of cell B2.
Further actions in VBA are depending if the right date combination exists in cell B2 or not.
I’ve tried the below code but that doesn’t work.

If Range("B2").Value = (MyMonth & "/" & MyYear) then

Any help or suggestions would be appreciated.

 

[sheetspread]

How about:

If month(Range("B2").Value) = MyMonth and Year(Range("B2").Value) = MyYear then

 

[Turbosnor]

I like the idea but somehow it doesn't (yet) work.
MyYear = 2018 and MyMonth = jan. It does check for the existence of 1/1/2018 but doesn't proceed to the Then but jumps to the Else statement ???

 

[Norie]

Does this work?

If Range("B2").Text = MyMonth & "/" & MyYear Then

 

[Turbosnor]

Thank you Norie but unfortunately your suggestion also only makes it jump to the Else statement and not execute what is behind the Then.

 

[Norie]

In the posted code there is no Else statement but if there is then it, again, indicates there's no match between the value in the cell and the variables.

How exactly are you setting the values of the variables and formatting the cell?

Does this work?

If Range("B2").Value = DateValue(MyMonth & "/" & MyYear)

 

[sheetspread]

MyMonth = jan.

Try:


If monthname(month(Range("B2").Value),true) = MyMonth and Year(Range("B2").Value) = MyYear then

 

[Turbosnor]

Thank you Norie, that works ! Your help is much appreciated. I spend several hours on this and can now continue. So much to learn

 

[Turbosnor]

Thank you Sheetspread, your solution also works. Different solutions possible in VBA. I'll check the difference between the 2 and then incorporate one in my code. I'm very pleased to get such good assistance during my trials and tribulations

 

[sheetspread]

Norie's solution is better, but the problem was that 1/1/2018 is actually 43101 (days since 1/0/1900). The 1/1/2018 shown in the window is just a format of that number, so to compare it to jan-18 or 1/18 (Excel assumes the first day of the month when none is entered) you need a function like datevalue or month, year, etc.