Prev: Listbox ?
Next: setting more than one filter
From: Phyllis on 22 Apr 2010 17:06
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
From: Dave Peterson on 22 Apr 2010 17:59
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
From: Phyllis on 23 Apr 2010 13:00
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
> .
>
From: Dave Peterson on 23 Apr 2010 14:38
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
 | 
Pages: 1
Prev: Listbox ?
Next: setting more than one filter