D
dzuy
Hello,
Does anyone know how to compare an input date with the current date? The
macro I'm using adds ordered item quantities to the current inventory balance
without regard to the current date. For example, if parts ordered are due on
06/30/06 and today's date is 06/29/06, the ordered part quantity immediately
adds it to the current balance.
Here's the full sub:
=========================
Sub macro2()
Dim orderpart(500, 1)
Dim orderpartqty(500, 2)
Sheets("On Order Parts").Select
countorder = 0
While (IsEmpty(Cells(2 + countorder, 1))) = False
countorder = countorder + 1
Wend
For i = 1 To countorder Step 1
orderpart(i, 1) = Cells(1 + i, 1)
orderpartqty(i, 2) = Cells(1 + i, 2)
Next i
'adds the number of parts in consolidated list
Sheets("Manage").Select
newlist = 0
While (IsEmpty(Cells(6 + newlist, 1))) = False
newlist = newlist + 1
Wend
'removes previous highlighted rows
Range(Cells(6, 1), Cells(newlist + 6, 7)).Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With
Range(Cells(6, 5), Cells(newlist + 6, 5)).Select
Selection.ClearContents
'adds qty for part number from "On Order Parts"
i = 0
j = 0
For i = 1 To countorder Step 1
For j = 1 To newlist Step 1
If Cells(5 + j, 1) = orderpart(i, 1) Then
Cells(5 + j, 6) = orderpartqty(i, 2)
End If
Next j
Next i
'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
End Sub
===========================
Thanks a bunch!
Does anyone know how to compare an input date with the current date? The
macro I'm using adds ordered item quantities to the current inventory balance
without regard to the current date. For example, if parts ordered are due on
06/30/06 and today's date is 06/29/06, the ordered part quantity immediately
adds it to the current balance.
Here's the full sub:
=========================
Sub macro2()
Dim orderpart(500, 1)
Dim orderpartqty(500, 2)
Sheets("On Order Parts").Select
countorder = 0
While (IsEmpty(Cells(2 + countorder, 1))) = False
countorder = countorder + 1
Wend
For i = 1 To countorder Step 1
orderpart(i, 1) = Cells(1 + i, 1)
orderpartqty(i, 2) = Cells(1 + i, 2)
Next i
'adds the number of parts in consolidated list
Sheets("Manage").Select
newlist = 0
While (IsEmpty(Cells(6 + newlist, 1))) = False
newlist = newlist + 1
Wend
'removes previous highlighted rows
Range(Cells(6, 1), Cells(newlist + 6, 7)).Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With
Range(Cells(6, 5), Cells(newlist + 6, 5)).Select
Selection.ClearContents
'adds qty for part number from "On Order Parts"
i = 0
j = 0
For i = 1 To countorder Step 1
For j = 1 To newlist Step 1
If Cells(5 + j, 1) = orderpart(i, 1) Then
Cells(5 + j, 6) = orderpartqty(i, 2)
End If
Next j
Next i
'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
End Sub
===========================
Thanks a bunch!