P
Phyllis
Following is code I am running. I intend to compare two dates. One date
(orderrg.value) is from a spreadsheet and is defined as a type date. The
other date is the system date. However when I check to see if the
orderrg.value is a date via ISDATE, it fails. Following the code is the
prinout from the immediate window. It looks to me like it should be able to
recognize it as a date, but yet it doesn't. Does anyone have any ideas?
Private Sub Workbook_Open()
Dim wscommission As Worksheet
Dim nindex As Integer
Dim lastorder As Integer
Dim sheetname As String
Dim orderws As Worksheet
Dim orderrg As range
Set wscommission = ThisWorkbook.Worksheets("commission")
Application.Cursor = xlNorthwestArrow
'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION
Debug.Print Date
lastorder = ThisWorkbook.Worksheets.count - 8 'sets ending range to 'the
last possible order
Debug.Print lastorder
For nindex = 3 To lastorder 'process 1st order thru the last order
sheetname = ThisWorkbook.Worksheets(nindex).Name 'set up the sheetname
Debug.Print sheetname
'Sheets(sheetname).Select
Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN
ASSIGNED)
If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
Set orderws = ThisWorkbook.Worksheets(sheetname)
Set orderrg = orderws.range("E24")
Debug.Print orderrg.Value
'Dorderdate = CDate(orderrg.Value)
' orderdate = orderrg.Value
If IsDate(Date) Then
If IsDate(orderrg.Value) Then
'include code to compare the dates once I get valid dates
Else
GoTo usererror
End If
Else
GoTo usererror
End If
Else
Exit Sub
End If
Next
usererror:
Debug.Print "date invalid"
End Sub
HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
4/22/2010
3
D100001
35
2/29/2010
date invalid
(orderrg.value) is from a spreadsheet and is defined as a type date. The
other date is the system date. However when I check to see if the
orderrg.value is a date via ISDATE, it fails. Following the code is the
prinout from the immediate window. It looks to me like it should be able to
recognize it as a date, but yet it doesn't. Does anyone have any ideas?
Private Sub Workbook_Open()
Dim wscommission As Worksheet
Dim nindex As Integer
Dim lastorder As Integer
Dim sheetname As String
Dim orderws As Worksheet
Dim orderrg As range
Set wscommission = ThisWorkbook.Worksheets("commission")
Application.Cursor = xlNorthwestArrow
'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION
Debug.Print Date
lastorder = ThisWorkbook.Worksheets.count - 8 'sets ending range to 'the
last possible order
Debug.Print lastorder
For nindex = 3 To lastorder 'process 1st order thru the last order
sheetname = ThisWorkbook.Worksheets(nindex).Name 'set up the sheetname
Debug.Print sheetname
'Sheets(sheetname).Select
Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN
ASSIGNED)
If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
Set orderws = ThisWorkbook.Worksheets(sheetname)
Set orderrg = orderws.range("E24")
Debug.Print orderrg.Value
'Dorderdate = CDate(orderrg.Value)
' orderdate = orderrg.Value
If IsDate(Date) Then
If IsDate(orderrg.Value) Then
'include code to compare the dates once I get valid dates
Else
GoTo usererror
End If
Else
GoTo usererror
End If
Else
Exit Sub
End If
Next
usererror:
Debug.Print "date invalid"
End Sub
HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
4/22/2010
3
D100001
35
2/29/2010
date invalid