| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My on-going problems with dates
I seem to always be having problems with Dates for some reason.
The below code. The followong finds the Value in Column A(rngfound) Then in rngfound.offset(0,3) i have a DATE (say" 23 March 2007) Then is rngfound.offset(0,5) i have a numerical value (Representing an Amount of Years) I want the code below to (At HIGHLIGHTED LINE) to ADD the Number of years(rngfound.offset(0,5)) to the DATE (rngfound.offset(0,3), to THUS give me an EXIRY Date. IF this DATE is LESS that(<=) to TODAY's Date then the MSGBOX Appears. If the Date is Greater then to Skip the MSGBOX. Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If Now <= rngFound.Offset(0, 3).Value + rngFound.Offset(0, 5) * "365.25" Then ' <=== HERE ' If Expired then WARNING Message MsgBox "The [ " & rngFound.Offset(0, 5) & " ] Warranty Period has Expired." & vbCrLf & vbCrLf & _ "Any Services carried out Should be Charged to [ " & rngFound.Offset(0, 2) & " ].", vbInformation Application.ScreenUpdating = True Exit Sub Else 'If Expiry Date is Not yet, then Carry on End If End With UserForm3.Show Unload Me Application.ScreenUpdating = True End Sub What am i doing wrong, as i get the MSGBOX even though i set the Date to Tomorrow's date to expire ? Corey.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My on-going problems with dates
Look into using the DateAdd function to get the expiry date.
NickHK "Coza" wrote in message ... I seem to always be having problems with Dates for some reason. The below code. The followong finds the Value in Column A(rngfound) Then in rngfound.offset(0,3) i have a DATE (say" 23 March 2007) Then is rngfound.offset(0,5) i have a numerical value (Representing an Amount of Years) I want the code below to (At HIGHLIGHTED LINE) to ADD the Number of years(rngfound.offset(0,5)) to the DATE (rngfound.offset(0,3), to THUS give me an EXIRY Date. IF this DATE is LESS that(<=) to TODAY's Date then the MSGBOX Appears. If the Date is Greater then to Skip the MSGBOX. Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If Now <= rngFound.Offset(0, 3).Value + rngFound.Offset(0, 5) * "365.25" Then ' <=== HERE ' If Expired then WARNING Message MsgBox "The [ " & rngFound.Offset(0, 5) & " ] Warranty Period has Expired." & vbCrLf & vbCrLf & _ "Any Services carried out Should be Charged to [ " & rngFound.Offset(0, 2) & " ].", vbInformation Application.ScreenUpdating = True Exit Sub Else 'If Expiry Date is Not yet, then Carry on End If End With UserForm3.Show Unload Me Application.ScreenUpdating = True End Sub What am i doing wrong, as i get the MSGBOX even though i set the Date to Tomorrow's date to expire ? Corey.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My on-going problems with dates
I keep getting an error.
Not sure how or what to set to a String and a Double?? "NickHK" wrote in message ... Look into using the DateAdd function to get the expiry date. NickHK "Coza" wrote in message ... I seem to always be having problems with Dates for some reason. The below code. The followong finds the Value in Column A(rngfound) Then in rngfound.offset(0,3) i have a DATE (say" 23 March 2007) Then is rngfound.offset(0,5) i have a numerical value (Representing an Amount of Years) I want the code below to (At HIGHLIGHTED LINE) to ADD the Number of years(rngfound.offset(0,5)) to the DATE (rngfound.offset(0,3), to THUS give me an EXIRY Date. IF this DATE is LESS that(<=) to TODAY's Date then the MSGBOX Appears. If the Date is Greater then to Skip the MSGBOX. Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If Now <= rngFound.Offset(0, 3).Value + rngFound.Offset(0, 5) * "365.25" Then ' <=== HERE ' If Expired then WARNING Message MsgBox "The [ " & rngFound.Offset(0, 5) & " ] Warranty Period has Expired." & vbCrLf & vbCrLf & _ "Any Services carried out Should be Charged to [ " & rngFound.Offset(0, 2) & " ].", vbInformation Application.ScreenUpdating = True Exit Sub Else 'If Expiry Date is Not yet, then Carry on End If End With UserForm3.Show Unload Me Application.ScreenUpdating = True End Sub What am i doing wrong, as i get the MSGBOX even though i set the Date to Tomorrow's date to expire ? Corey.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My on-going problems with dates
Got it to add the date, but it adds the yrs to the actual value in the
worksheet. I ONLY need the Listbox4 value to have the yrs added to it. Corey.... "Coza" wrote in message ... I keep getting an error. Not sure how or what to set to a String and a Double?? "NickHK" wrote in message ... Look into using the DateAdd function to get the expiry date. NickHK "Coza" wrote in message ... I seem to always be having problems with Dates for some reason. The below code. The followong finds the Value in Column A(rngfound) Then in rngfound.offset(0,3) i have a DATE (say" 23 March 2007) Then is rngfound.offset(0,5) i have a numerical value (Representing an Amount of Years) I want the code below to (At HIGHLIGHTED LINE) to ADD the Number of years(rngfound.offset(0,5)) to the DATE (rngfound.offset(0,3), to THUS give me an EXIRY Date. IF this DATE is LESS that(<=) to TODAY's Date then the MSGBOX Appears. If the Date is Greater then to Skip the MSGBOX. Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Me.ComboBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If Now <= rngFound.Offset(0, 3).Value + rngFound.Offset(0, 5) * "365.25" Then ' <=== HERE ' If Expired then WARNING Message MsgBox "The [ " & rngFound.Offset(0, 5) & " ] Warranty Period has Expired." & vbCrLf & vbCrLf & _ "Any Services carried out Should be Charged to [ " & rngFound.Offset(0, 2) & " ].", vbInformation Application.ScreenUpdating = True Exit Sub Else 'If Expiry Date is Not yet, then Carry on End If End With UserForm3.Show Unload Me Application.ScreenUpdating = True End Sub What am i doing wrong, as i get the MSGBOX even though i set the Date to Tomorrow's date to expire ? Corey.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Between dates. Two problems | Excel Discussion (Misc queries) | |||
| Problems with using 2008 in dates | Excel Discussion (Misc queries) | |||
| Problems with dates? | Excel Discussion (Misc queries) | |||
| problems with dates | Excel Discussion (Misc queries) | |||
| problems with dates before 1900 | Excel Programming | |||
Linear Mode
