代码:
- Option Explicit
- Sub test()
- Dim Arr, Brr(), DicDg, DicPh, DicJs
- Dim i&, DgY%, MsHh$, MsPh$, Ms$, a%, bj&
- Dim k1, k2, x&, pm%, j%, n%, li%, Hz
- Set DicDg = CreateObject("scripting.dictionary")
- Set DicPh = CreateObject("scripting.dictionary")
- Set DicJs = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Arr = Sheet1.[a1].CurrentRegion
- For i = 2 To UBound(Arr)
- DgY = Val(Mid(Arr(i, 5), 3))
- MsHh = Arr(i, 1) & Arr(i, 2)
- Ms = Arr(i, 7) & "|" & Arr(i, 5)
- MsPh = Arr(i, 4) & "#" & Arr(i, 6)
- If Not DicDg.exists(Arr(i, 7)) Then
- Set DicDg(Arr(i, 7)) = CreateObject("scripting.dictionary")
- End If
- DicDg(Arr(i, 7))(DgY) = DicDg(Arr(i, 7))(DgY) & "#" & MsHh
- DicPh(Ms) = MsPh
- DicJs(Ms) = DicJs(Ms) + 1
- Next i
- ReDim Brr(1 To UBound(Arr), 1 To 10)
- With Sheets("模板")
- .UsedRange.Offset(16).Clear
- With .Cells.Font
- .Size = 9: .Name = "微软雅黑"
- End With
- For Each k1 In DicDg.keys
- For i = 0 To UBound(DicDg(k1).keys)
- n = 0
- pm = WorksheetFunction.Small(DicDg(k1).keys, i + 1)
- Ms = k1 & "|" & "导购" & pm
- x = x + 1: bj = x
- Brr(x, 1) = "楼层": Brr(x, 2) = k1: Brr(x, 3) = "货物名称"
- Brr(x, 4) = Split(DicPh(Ms), "#")(0)
- Brr(x, 5) = "导购员": Brr(x, 6) = "导购" & pm: Brr(x, 7) = "货物数量"
- Brr(x, 8) = DicJs(Ms): Brr(x, 9) = "配货员": Brr(x, 10) = Split(DicPh(Ms), "#")(1)
- For a = 2 To 10 Step 2
- .Cells(x, a).Interior.Color = vbYellow
- Next a
- .Rows(x).HorizontalAlignment = xlCenter
- Hz = Split(Mid(DicDg(k1)(pm), 2), "#")
- x = x + 1
- For j = 0 To UBound(Hz)
- n = n + 1
- If n Mod 11 = 0 Then
- x = x + 1: n = 0: j = j - 1
- Else
- Brr(x, n) = Hz(j)
- End If
- Next j
- x = x + 1
- With .Cells(bj, 1).Resize(x - bj, 10)
- .Borders.LineStyle = 1
- .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
- End With
- Next i
- Next
- .[a1].Resize(x, 10) = Brr
- .Columns.AutoFit: Rows.RowHeight = 15
- End With
- Set DicDg = Nothing: Set DicJs = Nothing: Set DicPh = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码 |