|
日期<=J3 料号A4 第五列第7码<>W 第14列第三码<>R
Sub 返工()
Range("c4:j65536").ClearContents
Application.ScreenUpdating = False
Dim dc As Object, arr, brr(), k&, i&, a, b, z
Set dc = CreateObject("scripting.dictionary")
arr = Sheets("êy¾Y¿a").Range("a1").CurrentRegion
brr = Range("a3:k" & Range("a65536").End(3).Row)
ReDim crr(1 To UBound(arr), 1 To 12)
For j = 3 To UBound(brr, 2) - 1
dc(brr(1, j)) = j - 2
Next j
a = brr(2, 1) & brr(1, 11)
For i = 2 To UBound(arr)
If Mid(arr(i, 5), 7, 1) = "W" Then
arr(i, 16) = 0
End If
z = arr(i, 2) & arr(i, 13)
If dc.exists(arr(i, 1)) And z = a Then crr(3, dc(arr(i, 1))) = crr(3, dc(arr(i, 1))) + arr(i, 16)
If Mid(arr(i, 14), 3, 1) = "R" Then
arr(i, 16) = 0
End If
If dc.exists(arr(i, 1)) And z = a Then crr(2, dc(arr(i, 1))) = crr(2, dc(arr(i, 1))) + arr(i, 16)
Next
[c4].Resize(3, 10) = crr
Application.ScreenUpdating = True
For k = 3 To 10
Cells(4, k) = Cells(6, k) - Cells(5, k)
If Cells(6, k) = 0 Then
Cells(7, k) = ""
Else
Cells(7, k) = Cells(4, k) / Cells(6, k)
End If
Cells(8, k) = Range("L1")
Next k
Range("A10") = Format(Range("J3"), "yyyyÄêmmÔÂddèÕ") & ",èë¿aÕy3£Åú" & Range("J5") & "Ƭ,ÖØ1¤Åú" & Range("J5") & "Ƭ£¬×ü¼Æ" & Range("J6") & "Ƭ;"
Range("A11") = Format(Range("J3"), "yyyyÄêmmÔÂddèÕ") & ",èë¿aÖØ1¤Åú±èày" & Format(Range("J7"), "0.00%")
Range("A12") = Format(Range("J3"), "yyyyÄêmmÔÂddèÕ") & ",±¾ÔÂÖØ1¤Åú×üèë¿a" & Format(Range("J7"), "0.00%") & ",Õ¼±¾ÔÂèë¿a×üá¿"
End Sub
|
|