Date Code

[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

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

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


"aftamath" <aftamath@discussions.microsoft.com> wrote in message
news:305D2981-B363-4018-9F57-0E10A3BCDD56@microsoft.com...
> 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
>

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

"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" <aftamath@discussions.microsoft.com> wrote in message
> news:305D2981-B363-4018-9F57-0E10A3BCDD56@microsoft.com...
> > 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]

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" <aftamath@discussions.microsoft.com> wrote in message
> > news:305D2981-B363-4018-9F57-0E10A3BCDD56@microsoft.com...
> > > 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