|
本帖最后由 ykcbf1100 于 2024-4-30 13:29 编辑
参与一下。。。
- Sub ykcbf() '//2024.4.30
- Dim arr, brr, d, s
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
-
- Set sh = ThisWorkbook.Sheets("Sheet2")
- bt = [{"销","存"}]
- m = 2: n = 1
- On Error Resume Next
- With Sheets("Sheet1")
- r = .Cells(Rows.Count, 1).End(3).Row
- arr = .[a1].Resize(r, 4)
- ReDim brr(1 To UBound(arr), 1 To 300)
- brr(1, 1) = arr(1, 2)
- For i = 2 To UBound(arr)
- If arr(i, 1) <> Empty Then
- s = CStr(arr(i, 2))
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = s
- End If
- s = CStr(arr(i, 1))
- If Not d.exists(s) Then
- n = n + 2
- d(s) = n
- brr(1, n - 1) = s
- brr(2, n - 1) = bt(1): brr(2, n) = bt(2)
- End If
- r = d(CStr(arr(i, 2))): c = d(CStr(arr(i, 1)))
- brr(r, c - 1) = brr(r, c - 1) + arr(i, 3)
- brr(r, c) = brr(r, c) + arr(i, 4)
- End If
- Next
- End With
- With sh
- .UsedRange.Clear
- .Columns(1).NumberFormatLocal = "@"
- .[a1].Resize(m, n) = brr
- .[a1].Resize(2, n).Interior.Color = 49407
- .[a3].Resize(m - 2, 1).Interior.Color = 5296274
- .[a1].Resize(2).Merge
- For j = 2 To n Step 2
- .Cells(1, j).Resize(1, 2).Merge
- Next
- ActiveWindow.DisplayZeros = False
- Set Rng = .[a1].Resize(m, n)
- With Rng
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- With .Font
- .Name = "微软雅黑"
- .Size = 11
- End With
- End With
- End With
- Set d = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|