Sub 單品數量統計1()
Dim sh As Worksheet, c As Range, r&, col%, arr, tmp
Set sh = Sheets("數據表")
With sh
r = .Cells.SpecialCells(xlCellTypeLastCell).Row
arr = .Range(.Cells(1, 1), .Cells(IIf(r > 10000, 10000, r), 33))
For r = 4 To UBound(arr)
Cells(r, 21) = arr(r, 5)
If arr(r, 4) <> "" Then
Cells(r, 6) = Month(arr(r, 4))
If Year(arr(r, 4)) = Cells(1, 1) And arr(r, 6) <> "" Then
For col = 22 To 33
If Cells(3, col) = Month(arr(r, 4)) Then
Cells(r, col) = arr(r, 6)
Cells(4, 9).Resize(1, 2) = arr(4, 10)
End If
Next
If arr(r, 6) > 0 Then
Cells(r, 7) = arr(r, 6) * arr(r, 7)
Cells(r, 8) = arr(r, 6) * arr(r, 8)
Cells(r, 1) = arr(r, 4)
ElseIf arr(r, 6) < 0 Then
Cells(r, 11) = arr(r, 6) * arr(r, 7)
If arr(r, 9) <> "" Then Cells(r, 2) = arr(r, 4)
End If
End If
End If
Next
End With
End Sub