Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Comparison
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Comparison
Current date you can get via Date function, then
compare it with any date using ,< and so on. If your input date variable type is not "date", ex. string, use cdate() function beforehand. "dzuy" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Comparison | Excel Discussion (Misc queries) | |||
Date Comparison | Excel Worksheet Functions | |||
date comparison | Excel Discussion (Misc queries) | |||
VBA: Date Comparison | Excel Programming | |||
Date comparison | Excel Discussion (Misc queries) |