LLL
07-22-2009, 10:38 AM
Hello all. I am having trouble with my code returning improper dates:
Example: The date in the form may be 9/3/2009. When it performs the add function it returns something like this 1/11/1901, instead returning the date 9/3/2010, as I would like for it to. Here is a snippet of the code that is the problem I am guessing. At the end of this code you will see the date add function. Any guesses as to if the way this is entered may be causing this. Thanks for any help!
Case 26
Dim lngCurrentRow As Long
lngCurrentRow = Selection.Row
Dim intMessage As Integer
Dim intMessage2 As Integer
Dim strInterval As String
Dim PaidThruDate As Integer
Dim intNumber As Integer
Dim WBErow2 As Long
Dim WBEcol1 As Long
strInterval = "yyyy"
intNumber = 1
WBEcol1 = 12
If IsDate(Cells(lngCurrentRow, 26).Value) = True Then
If MatchTheCriteria2(lngCurrentRow) = 0 Then
MsgBox "Not yet in the WBE Profile2 file " & vbCrLf & _
"Record not found in WBE Profile2 matching this record"
Exit Sub
Else
WBErow2 = MatchTheCriteria2(lngCurrentRow)
intMessage2 = MsgBox("Are you sure about this data?" & vbCrLf & _
Range("A" & lngCurrentRow).Value & vbCrLf & _
Range("B" & lngCurrentRow).Value & vbCrLf & _
Range("C" & lngCurrentRow).Value & vbCrLf & _
Range("D" & lngCurrentRow).Value & vbCrLf & _
Range("E" & lngCurrentRow).Value & vbCrLf & _
Range("F" & lngCurrentRow).Value & vbCrLf & _
Range("G" & lngCurrentRow).Value & vbCrLf & _
Range("H" & lngCurrentRow).Value & vbCrLf & _
Range("I" & lngCurrentRow).Value & vbCrLf & _
Range("J" & lngCurrentRow).Value & vbCrLf & _
Range("K" & lngCurrentRow).Value, vbOKCancel + vbQuestion + vbApplicationModal, "Query")
Select Case intMessage2
Case vbOK
Workbooks("WBE Profile2.xls").Worksheets("WBE_Profile").Cells(WBErow2, WBEcol1).Value = DateAdd(strInterval, intNumber, 12)
Cells(lngCurrentRow, 26).EntireRow.Delete
MsgBox "Successfully entered the data to WBE Profile2.xls"
Case vbCancel
End Select
Example: The date in the form may be 9/3/2009. When it performs the add function it returns something like this 1/11/1901, instead returning the date 9/3/2010, as I would like for it to. Here is a snippet of the code that is the problem I am guessing. At the end of this code you will see the date add function. Any guesses as to if the way this is entered may be causing this. Thanks for any help!
Case 26
Dim lngCurrentRow As Long
lngCurrentRow = Selection.Row
Dim intMessage As Integer
Dim intMessage2 As Integer
Dim strInterval As String
Dim PaidThruDate As Integer
Dim intNumber As Integer
Dim WBErow2 As Long
Dim WBEcol1 As Long
strInterval = "yyyy"
intNumber = 1
WBEcol1 = 12
If IsDate(Cells(lngCurrentRow, 26).Value) = True Then
If MatchTheCriteria2(lngCurrentRow) = 0 Then
MsgBox "Not yet in the WBE Profile2 file " & vbCrLf & _
"Record not found in WBE Profile2 matching this record"
Exit Sub
Else
WBErow2 = MatchTheCriteria2(lngCurrentRow)
intMessage2 = MsgBox("Are you sure about this data?" & vbCrLf & _
Range("A" & lngCurrentRow).Value & vbCrLf & _
Range("B" & lngCurrentRow).Value & vbCrLf & _
Range("C" & lngCurrentRow).Value & vbCrLf & _
Range("D" & lngCurrentRow).Value & vbCrLf & _
Range("E" & lngCurrentRow).Value & vbCrLf & _
Range("F" & lngCurrentRow).Value & vbCrLf & _
Range("G" & lngCurrentRow).Value & vbCrLf & _
Range("H" & lngCurrentRow).Value & vbCrLf & _
Range("I" & lngCurrentRow).Value & vbCrLf & _
Range("J" & lngCurrentRow).Value & vbCrLf & _
Range("K" & lngCurrentRow).Value, vbOKCancel + vbQuestion + vbApplicationModal, "Query")
Select Case intMessage2
Case vbOK
Workbooks("WBE Profile2.xls").Worksheets("WBE_Profile").Cells(WBErow2, WBEcol1).Value = DateAdd(strInterval, intNumber, 12)
Cells(lngCurrentRow, 26).EntireRow.Delete
MsgBox "Successfully entered the data to WBE Profile2.xls"
Case vbCancel
End Select