- Private Sub CommandButton1_Click()
- Dim i&, Myr&, b, Arr, Arr1, x$, j&, ii&, da, a12, jj&
- Dim d, k, t, d1, k1, t1, aa, bb, k11, k22, ctr$
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Myr = Sheet1.[c65536].End(xlUp).Row
- Arr = Sheet1.Range("a5:aa" & Myr)
- For i = 1 To UBound(Arr)
- x = Arr(i, 3) & "|" & Arr(i, 5) & "|" & Arr(i, 13) & "|" & Arr(i, 15) & "|" & Arr(i, 16) & "|" & Arr(i, 25) & "|" & Arr(i, 26)
- d(x) = d(x) + Arr(i, 11)
- d1(x) = d1(x) & i & ","
- Next
- ReDim Arr1(1 To d.Count, 1 To 13)
- k = d.keys
- t = d.items
- t1 = d1.items
- d.RemoveAll
- d1.RemoveAll
- For i = 0 To UBound(k)
- da = #12/31/2009#: ctr = ""
- aa = Split(k(i), "|")
- Arr1(i + 1, 1) = aa(0)
- Arr1(i + 1, 2) = aa(2)
- Arr1(i + 1, 3) = aa(3)
- Arr1(i + 1, 4) = aa(4)
- Arr1(i + 1, 7) = DateValue(aa(6))
- Arr1(i + 1, 9) = aa(5)
- Arr1(i + 1, 10) = t(i)
- b = Left(t1(i), Len(t1(i)) - 1)
- If InStr(b, ",") > 0 Then
- bb = Split(b, ",")
- For j = 0 To UBound(bb)
- If aa(1) = "3RD PARTY" Then
- ctr = ctr & Arr(bb(j), 4) & "/"
- Else
- If InStr(ctr, Arr(bb(j), 4)) = 0 Then
- ctr = ctr & Arr(bb(j), 4) & "/"
- End If
- End If
- d(Arr(bb(j), 22)) = ""
- d1(Arr(bb(j), 23)) = ""
- Next
- If Len(ctr) - Len(Replace(ctr, "/", "")) > 1 Then
- Arr1(i + 1, 12) = ctr
- Else
- Arr1(i + 1, 12) = aa(1)
- End If
- For j = 0 To UBound(bb)
- If Arr(bb(j), 27) = "TBA" Then
- Arr1(i + 1, 8) = "TBA": Exit For
- ElseIf Arr(bb(j), 27) = "NO ETD" Then
- Arr1(i + 1, 8) = "NO ETD": Exit For
- Else
- If Arr(bb(j), 27) > da Then
- da = Arr(bb(j), 27)
- End If
- Arr1(i + 1, 8) = da
- End If
- Next
- If Arr1(i + 1, 8) <> "TBA" And Arr1(i + 1, 8) <> "NO ETD" Then
- Arr1(i + 1, 8) = Arr1(i + 1, 8)
- End If
- k11 = d.keys
- k22 = d1.keys
- For ii = 0 To UBound(k11)
- Arr1(i + 1, 5) = Arr1(i + 1, 5) & "WK" & k11(ii) & "/"
- Next
- For ii = 0 To UBound(k22)
- Arr1(i + 1, 6) = Arr1(i + 1, 6) & k22(ii) & "/"
- Next
- If InStr(Arr1(i + 1, 5), "/") > 0 Then
- Arr1(i + 1, 5) = Left(Arr1(i + 1, 5), Len(Arr1(i + 1, 5)) - 1)
- End If
- If InStr(Arr1(i + 1, 6), "/") > 0 Then
- Arr1(i + 1, 6) = Left(Arr1(i + 1, 6), Len(Arr1(i + 1, 6)) - 1)
- End If
- d.RemoveAll
- If InStr(Arr1(i + 1, 12), "/") > 0 Then
- Arr1(i + 1, 12) = Left(Arr1(i + 1, 12), Len(Arr1(i + 1, 12)) - 1)
- a12 = Split(Arr1(i + 1, 12), "/")
- For jj = 0 To UBound(a12)
- d(a12(jj)) = ""
- Next
- Arr1(i + 1, 12) = Join(d.keys, "/")
- End If
- Else
- Arr1(i + 1, 12) = Arr(Val(b), 4)
- Arr1(i + 1, 5) = "WK" & Arr(Val(b), 22)
- Arr1(i + 1, 6) = Arr(Val(b), 23)
- Arr1(i + 1, 8) = Arr(Val(b), 27)
- End If
- d.RemoveAll
- d1.RemoveAll
- If Arr1(i + 1, 8) = "" Then
- Arr1(i + 1, 11) = "No Claddified"
- ElseIf Arr1(i + 1, 8) = "NO ETD" Then
- Arr1(i + 1, 11) = "No planned ETD"
- ElseIf Arr1(i + 1, 8) = "TBA" Then
- Arr1(i + 1, 11) = "PC can't provide planned ETD per schedule, assume they are late"
- ElseIf CDate(Arr1(i + 1, 7)) >= CDate(Arr1(i + 1, 8)) Then
- Arr1(i + 1, 11) = "On-time"
- ElseIf CDate(Arr1(i + 1, 8)) - CDate(Arr1(i + 1, 7)) <= 14 Then
- Arr1(i + 1, 11) = "Delay 1"
- Else
- Arr1(i + 1, 11) = "Delay 2"
- End If
- If Arr1(i + 1, 11) = "Delay 2" Or Arr1(i + 1, 11) = "Delay 1" Or Arr1(i + 1, 8) = "TBA" Then
- Arr1(i + 1, 13) = "RED"
- ElseIf Arr1(i + 1, 11) = "On-time" Then
- Arr1(i + 1, 13) = "GREEN"
- ElseIf Arr1(i + 1, 8) = "NO ETD" Then
- Arr1(i + 1, 13) = "WHITE"
- Else
- Arr1(i + 1, 13) = ""
- End If
- Next
- Sheet2.Activate
- [a11:m1000].ClearContents
- [a11].Resize(UBound(Arr1), 13) = Arr1
- Set d = Nothing
- Set d1 = Nothing
- End Sub
复制代码 |