|
用字典写的代码,如果日期过多则过滤区会被覆盖
- Sub TJ1()
- Dim I&, J&, K&, Dic1, Dic2, Dic3, Arr, Brr, Crr
- Set Dic1 = CreateObject("Scripting.Dictionary")
- Set Dic2 = CreateObject("Scripting.Dictionary")
- Set Dic3 = CreateObject("Scripting.Dictionary")
- Arr = Range("A3:D" & [A65535].End(xlUp).Row)
- Brr = Range("P1:P" & [P65535].End(xlUp).Row)
- For I = 1 To UBound(Brr)
- Dic1(Brr(I, 1)) = ""
- Next
- For I = 1 To UBound(Arr)
- If Not Dic2.Exists(Arr(I, 3)) Then J = J + 1: Dic2(Arr(I, 3)) = J
- If Not Dic3.Exists(Arr(I, 4)) Then K = K + 1: Dic3(Arr(I, 4)) = K
- Next
- ReDim Crr(1 To Dic2.Count, 1 To Dic3.Count)
- For I = 1 To UBound(Arr)
- If Not Dic1.Exists(Arr(I, 1)) Then Crr(Dic2(Arr(I, 3)), Dic3(Arr(I, 4))) = Crr(Dic2(Arr(I, 3)), Dic3(Arr(I, 4))) + Arr(I, 2)
- Next
- Range("F2:O" & [F2].End(xlDown).Row).ClearContents
- [F2] = "产品ID\日期"
- [G2].Resize(, Dic3.Count) = Dic3.Keys
- [F3].Resize(Dic2.Count) = Application.Transpose(Dic2.Keys)
- [G3].Resize(Dic2.Count, Dic3.Count) = Crr
- Set Dic1 = Nothing
- Set Dic2 = Nothing
- Set Dic3 = Nothing
- End Sub
复制代码 |
|