Date Code
 

Here is my code that I'm having a problem with. I want the programming to
start evaluating from cell I2 through the rest of the column. Is there a
different way to write the code. Well the workbook opens, I get a message
that says 'Type Mismatch' and highlights the boolean section of the if
statement.

Private Sub Workbook_Open()

Dim Target As Range

Application.Workbooks(1).Worksheets(5).Activate

ActiveSheet.Range("I2").Select

For Each Target In Range("I2:I20")

If Target.Value < "" And DateValue(Target.Value) - DateValue(Now) <= 2
And DateValue(Target.Value) - DateValue(Now) 0 And Target.Offset
_(columnoffset:=-1).Value = "" Then
MsgBox "Order Due" & " " & Target.Offset(columnoffset:=-7).Value
End If

Next Target

End Sub

Date Code
 

The "type mismatch" error indicates the code has encountered an
unexpected value. Since the IF is dealing with dates, is there a value
in i2:i20 that is not an Excel-approved date? That is to say, perhaps
a text entry that *appears* to be a date, instead of a serial date
value that is formatted to show as a date? Or maybe a blank entry?

Date Code
 

This seems to work, but are you sure about Now, that includes Time as well
as date

Private Sub Workbook_Open()
Dim Target As Range
Dim rng As Range

Application.Workbooks(5).Worksheets(1).Activate

ActiveSheet.Range("I2").Select

Set rng = Range("I2").Resize(, Cells(2, Columns.Count).End(xlToLeft).Column)
For Each Target In rng
With Target
If .Value < "" And (.Value - Now) <= 2 And _
(.Value - Now) 0 And _
.Offset(, -1).Value = "" Then
MsgBox "Order Due" & " " & .Offset(, -7).Value
End If
End With

Next Target

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aftamath" wrote in message
...
Here is my code that I'm having a problem with. I want the programming to
start evaluating from cell I2 through the rest of the column. Is there a
different way to write the code. Well the workbook opens, I get a message
that says 'Type Mismatch' and highlights the boolean section of the if
statement.

Private Sub Workbook_Open()

Dim Target As Range

Application.Workbooks(1).Worksheets(5).Activate

ActiveSheet.Range("I2").Select

For Each Target In Range("I2:I20")

If Target.Value < "" And DateValue(Target.Value) - DateValue(Now) <=
2
And DateValue(Target.Value) - DateValue(Now) 0 And
Target.Offset
_(columnoffset:=-1).Value = "" Then
MsgBox "Order Due" & " " &
Target.Offset(columnoffset:=-7).Value
End If

Next Target

End Sub

Date Code
 

Not really sure about the Now. Is there another function that returns just
that current date that I can use. All of the cell formats are date format.

"Bob Phillips" wrote:

This seems to work, but are you sure about Now, that includes Time as well
as date

Private Sub Workbook_Open()
Dim Target As Range
Dim rng As Range

Application.Workbooks(5).Worksheets(1).Activate

ActiveSheet.Range("I2").Select

Set rng = Range("I2").Resize(, Cells(2, Columns.Count).End(xlToLeft).Column)
For Each Target In rng
With Target
If .Value < "" And (.Value - Now) <= 2 And _
(.Value - Now) 0 And _
.Offset(, -1).Value = "" Then
MsgBox "Order Due" & " " & .Offset(, -7).Value
End If
End With

Next Target

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aftamath" wrote in message
...
Here is my code that I'm having a problem with. I want the programming to
start evaluating from cell I2 through the rest of the column. Is there a
different way to write the code. Well the workbook opens, I get a message
that says 'Type Mismatch' and highlights the boolean section of the if
statement.

Private Sub Workbook_Open()

Dim Target As Range

Application.Workbooks(1).Worksheets(5).Activate

ActiveSheet.Range("I2").Select

For Each Target In Range("I2:I20")

If Target.Value < "" And DateValue(Target.Value) - DateValue(Now) <=
2
And DateValue(Target.Value) - DateValue(Now) 0 And
Target.Offset
_(columnoffset:=-1).Value = "" Then
MsgBox "Order Due" & " " &
Target.Offset(columnoffset:=-7).Value
End If

Next Target

End Sub

Date Code
 

How about Date?

... (.value - date) <= ...



aftamath wrote:

Not really sure about the Now. Is there another function that returns just
that current date that I can use. All of the cell formats are date format.

"Bob Phillips" wrote:

This seems to work, but are you sure about Now, that includes Time as well
as date

Private Sub Workbook_Open()
Dim Target As Range
Dim rng As Range

Application.Workbooks(5).Worksheets(1).Activate

ActiveSheet.Range("I2").Select

Set rng = Range("I2").Resize(, Cells(2, Columns.Count).End(xlToLeft).Column)
For Each Target In rng
With Target
If .Value < "" And (.Value - Now) <= 2 And _
(.Value - Now) 0 And _
.Offset(, -1).Value = "" Then
MsgBox "Order Due" & " " & .Offset(, -7).Value
End If
End With

Next Target

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"aftamath" wrote in message
...
Here is my code that I'm having a problem with. I want the programming to
start evaluating from cell I2 through the rest of the column. Is there a
different way to write the code. Well the workbook opens, I get a message
that says 'Type Mismatch' and highlights the boolean section of the if
statement.

Private Sub Workbook_Open()

Dim Target As Range

Application.Workbooks(1).Worksheets(5).Activate

ActiveSheet.Range("I2").Select

For Each Target In Range("I2:I20")

If Target.Value < "" And DateValue(Target.Value) - DateValue(Now) <=
2
And DateValue(Target.Value) - DateValue(Now) 0 And
Target.Offset
_(columnoffset:=-1).Value = "" Then
MsgBox "Order Due" & " " &
Target.Offset(columnoffset:=-7).Value
End If

Next Target

End Sub





--

Dave Peterson