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

2010 isn't a leap year, so 2/29/2010 isn't a date.



Phyllis wrote:

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

--

Dave Peterson

Thank you Dave,
boy do I feel stupid. I was so busy concentrating on what was wrong with
the date format that it never occured to me.

"Dave Peterson" wrote:

2010 isn't a leap year, so 2/29/2010 isn't a date.



Phyllis wrote:

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

--

Dave Peterson
.

Sometimes, those kinds of errors just need an extra pair of eyes.

Phyllis wrote:

Thank you Dave,
boy do I feel stupid. I was so busy concentrating on what was wrong with
the date format that it never occured to me.

"Dave Peterson" wrote:

2010 isn't a leap year, so 2/29/2010 isn't a date.



Phyllis wrote:

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

--

Dave Peterson
.


--

Dave Peterson