My on-going problems with dates

C

Coza

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....
 
C

Coza

I keep getting an error.
Not sure how or what to set to a String and a Double??

NickHK said:
Look into using the DateAdd function to get the expiry date.

NickHK

Coza said:
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....
Click to expand...
Click to expand...
 
C

Coza

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 said:
I keep getting an error.
Not sure how or what to set to a String and a Double??

NickHK said:
Look into using the DateAdd function to get the expiry date.

NickHK

Coza said:
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....
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there something wrong with my code? 5
rngFound not finding correct value when more than one match 0
Keep Original Sheets Font Format when Populating data into other sheet 3
Stop TextBox Clearing Cell 3
Update existing data or Insert new problem 3
Code does not seem to work, but it should. 2
Making a macro only work on certain files 2
Add 2nd condition to Find 3

Top