| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine month day and year from separate columns in to one cell | Excel Discussion (Misc queries) | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| how can I highlight a cell if it meets year and month criteria | Excel Worksheet Functions | |||
| Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
| Month and year programmatically in a cell? | Excel Programming | |||
Linear Mode
