Get Month and Year ONLY from cell value
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.... |
Get Month and Year ONLY from cell value
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" wrote in message ... 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.... |
Get Month and Year ONLY from cell value
Got a work around to do the job for me.
"Corey" wrote in message ... 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" wrote in message ... 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.... |
All times are GMT +1. The time now is 08:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com