|
- Sub grf()
- Dim arr, brr, d, d1, i&, w()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- w = Array("货号", "M", "L") ', "XL")
- Dim XL(100) '最大可以99XL,够了吧
- arr = Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = ""
- cc = arr(i, 2) '尺寸
- If cc Like "*XL" Then XL(Val(cc)) = cc
- x = arr(i, 1) & arr(i, 2)
- d1(x) = d1(x) + arr(i, 3)
- Next
- n = 2
- For i = 0 To UBound(XL)
- If Len(XL(i)) > 0 Then
- n = n + 1
- ReDim Preserve w(n)
- w(n) = XL(i)
- End If
- Next
- [g1].Resize(1, UBound(w) + 1) = w
- [g2].Resize(d.Count, 1) = Application.Transpose(d.keys)
- brr = [g1].CurrentRegion
- For i = 2 To UBound(brr)
- For j = 2 To UBound(brr, 2)
- brr(i, j) = d1(brr(i, 1) & brr(1, j))
- Next
- Next
- [g1].CurrentRegion = brr
- End Sub
复制代码 |
|