Date Code

A

aftamath

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
 
D

Dave O

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?
 
B

Bob Phillips

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)
 
A

aftamath

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.
 
D

Dave Peterson

How about Date?

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


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.
Click to expand...
 
Top