Get Month and Year ONLY from cell value

[Corey]

The below code i am trying to get to promtp the user IF a monthly report has
not been sent off.
When the workbook OPENS it want the msgbox to POPUP IF the Current Month &
Year is NOT in a cell.
The Range of cells i have dates in is Range("C19:C30")
These cells are formatted to:
mmmm yyyy "Report Done"
IE. January 2007 Report Done would be displayed in Cell C19 and so on down
the rows to row 30 Feb-Dec 2007, then Column E19:E30 for 2008.

I am getting the msgbox at ALL times even if there is a date in the cell C19
for this month.
I think it maybe cause the Cell C18 is formatted as : Tuesday 9 January 2007
and Cell C19:C30 is formatted as January 2007.


Private Sub Workbook_Open()
Sheet1.Range("C18").Value = Now()
With Sheet1
If Range("C18").Value = Range("B19").Value And Range("C19").Value = "" Then
GoTo message
If Range("C18").Value = Range("B20").Value And Range("C20").Value = "" Then
GoTo message
If Range("C18").Value = Range("B21").Value And Range("C21").Value = "" Then
GoTo message
If Range("C18").Value = Range("B22").Value And Range("C22").Value = "" Then
GoTo message
If Range("C18").Value = Range("B23").Value And Range("C23").Value = "" Then
GoTo message
If Range("C18").Value = Range("B24").Value And Range("C24").Value = "" Then
GoTo message
If Range("C18").Value = Range("B25").Value And Range("C25").Value = "" Then
GoTo message
If Range("C18").Value = Range("B26").Value And Range("C26").Value = "" Then
GoTo message
If Range("C18").Value = Range("B27").Value And Range("C27").Value = "" Then
GoTo message
If Range("C18").Value = Range("B28").Value And Range("C28").Value = "" Then
GoTo message
If Range("C18").Value = Range("B29").Value And Range("C29").Value = "" Then
GoTo message
If Range("C18").Value = Range("B30").Value And Range("C30").Value = "" Then
GoTo message
message:
MsgBox "Report Due for the Month!!!", , " ...."
End With
End Sub


So i think i need to extract ONLY the Month & Year so i can MATCH values.

How would i do that ?


Corey....

 

[Corey]

What about something more like:

Private Sub Workbook_Open()
Sheet1.Range("C18").Value = Now()
Sheet1.Range("E18").Value = Now()
Dim rngFound As Range
On Error Resume Next
With Sheet1.Range("C19:C30")
Set rngFound = .Find(what:=Range("C18").Value, after:=.Range("C18"),
LookIn:=xlMonth, lookat:=xlPart, searchorder:=xlByRows,
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)

If rngFound <> "" Then Exit Sub
If rngFound = "" Then
MsgBox "Report Due for the Month!!!", , "...."
End If
End With
End Sub

But i still get no msgbox when i need to though ?

 

[Corey]

Got a work around to do the job for me.

What about something more like:

Private Sub Workbook_Open()
Sheet1.Range("C18").Value = Now()
Sheet1.Range("E18").Value = Now()
Dim rngFound As Range
On Error Resume Next
With Sheet1.Range("C19:C30")
Set rngFound = .Find(what:=Range("C18").Value, after:=.Range("C18"),
LookIn:=xlMonth, lookat:=xlPart, searchorder:=xlByRows,
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)

If rngFound <> "" Then Exit Sub
If rngFound = "" Then
MsgBox "Report Due for the Month!!!", , "...."
End If
End With
End Sub

But i still get no msgbox when i need to though ?