VBA Coding: Date Consideration

[dzuy]

I am currently working with an inventory Excel spreadsheet. There are two
worksheets: "Manage" and "On Order Parts".

Whenever I input an order into "On Order Parts" with order due date and
quantity information. The "Manage" worksheet updates the parts balance with
the on-order-quantity and does not consider the due date (it adds the
on-order-quantity regardless of the order due date). I've isolated the
problem to the segment of the macro that would need to be modified in order
to consider the due date:

===============================

'Updates Qty on order to Balance
For t = 1 To newlist Step 1

Cells(5 + t, 4) = Cells(5 + t, 2) - Cells(5 + t, 3) + Cells(5 + t, 6)
If Cells(5 + t, 4) <= 20 Then
Cells(5 + t, 5) = "Order Parts!"
Range(Cells(5 + t, 1), Cells(5 + t, 7)).Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
If Cells(5 + t, 4) > 20 And Cells(5 + t, 4) <= 50 Then
Cells(5 + t, 5) = "Balance Low"
Range(Cells(5 + t, 1), Cells(5 + t, 7)).Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next t
Cells(6, 1).Select

==================================

I think it should have an IF statement before the line:

Cells(5 + t, 4) = Cells(5 + t, 2) - Cells(5 + t, 3) + Cells(5 + t, 6)

But I don't know how to reference cells from another worksheet in VBA and
compare it to the current date. Maybe something like this (with the correct
syntax, of course):

If **Due Date** - **Today's Date** >= 0 Then

I know it just needs this one line! Can anyone help me out? Thanks!

[Irina]

To reference cells from the other sheet, use worksheets(index).cells(row,col)
or worksheets(index).cells(row,col)
or activeworkbook.worksheets(index).cells(row,col) or
activeworkbook.sheets(index).cells(row,col) if you workbook is active.
Instead of index you can use name of sheet. Ex. Sheets("Sheet1").
To refer to ActiveSheet use ActiveSheet.cels(row,col).
To get current date, use Date function.

regards, Irina



"dzuy" wrote:

> I am currently working with an inventory Excel spreadsheet. There are two
> worksheets: "Manage" and "On Order Parts".
>
> Whenever I input an order into "On Order Parts" with order due date and
> quantity information. The "Manage" worksheet updates the parts balance with
> the on-order-quantity and does not consider the due date (it adds the
> on-order-quantity regardless of the order due date). I've isolated the
> problem to the segment of the macro that would need to be modified in order
> to consider the due date:
>
> ===============================
>
> 'Updates Qty on order to Balance
> For t = 1 To newlist Step 1
>
> Cells(5 + t, 4) = Cells(5 + t, 2) - Cells(5 + t, 3) + Cells(5 + t, 6)
> If Cells(5 + t, 4) <= 20 Then
> Cells(5 + t, 5) = "Order Parts!"
> Range(Cells(5 + t, 1), Cells(5 + t, 7)).Select
> Selection.Font.Bold = True
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> End If
> If Cells(5 + t, 4) > 20 And Cells(5 + t, 4) <= 50 Then
> Cells(5 + t, 5) = "Balance Low"
> Range(Cells(5 + t, 1), Cells(5 + t, 7)).Select
> Selection.Font.Bold = True
> With Selection.Interior
> .ColorIndex = 36
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> End If
> Next t
> Cells(6, 1).Select
>
> ==================================
>
> I think it should have an IF statement before the line:
>
> Cells(5 + t, 4) = Cells(5 + t, 2) - Cells(5 + t, 3) + Cells(5 + t, 6)
>
> But I don't know how to reference cells from another worksheet in VBA and
> compare it to the current date. Maybe something like this (with the correct
> syntax, of course):
>
> If **Due Date** - **Today's Date** >= 0 Then
>
> I know it just needs this one line! Can anyone help me out? Thanks!
>
>