How to identify if a column contains the current month and year using VBA?

[Indominus]

Hello. I am trying to use VBA to identify if column A contains the current month and year then do the rest of my code versus do nothing. Thee dates were put in via VBA so the day on them are the 1st. If that matters. So I want Excel to indenting right now if column A contains any cell with May-21 (full is 5/1/2021). The format of the dates in column A are custom format “mmm-yy”. Here is the code I have so far however, it does not recognize it. Thanks

Sub LocateDate()



Dim rng1 As Range



Dim strSearch As String

strSearch = Format(Date, "mmm yyyy")

Set rng1 = Range("A:A").Find(strSearch, , xlValues, xlWhole)

If Not rng1 Is Nothing Then

MsgBox ("Contains date")

Else

MsgBox ("Does not contain date")

End If



End Sub

 

[mart37]

Maybe this works:

Dim strSearch As Date
strSearch = DateSerial(Year(Date), Month(Date), 1)
Set rng1 = Range("A:A").Find(CDate(strSearch), , xlFormulas, xlWhole)

 

[Indominus]

Maybe this works:

Dim strSearch As Date
strSearch = DateSerial(Year(Date), Month(Date), 1)
Set rng1 = Range("A:A").Find(CDate(strSearch), , xlFormulas, xlWhole)

Thank you this works. One more thing I just noticed if you do not mind. How would I identify if column A has the previous month and year in it. So April-21? Putting a “-1” after the Date is not working. Thanks

 

[Indominus]

Thank you this works. One more thing I just noticed if you do not mind. How would I identify if column A has the previous month and year in it. So April-21? Putting a “-1” after the Date is not working. Thanks

Never mind I figured it out. Thank you so much Mart37! I really appreciate it.