参与一下。。。- Sub ykcbf() '//2024.1.1
- Dim arr, brr, d, d1
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- bm = Array("办公室", "邯郸玉兰苑办公室", _
- "邯郸美的广场", "邯郸翡翠蓝山办公室", "天津美的薛军顺", "邯郸凤麟府", "石家庄陈瑞民", "黄渠办公室", _
- "黄渠临水临电", "音乐园王文革组", "东坝七颗树临水临电", "半壁店陈志国", "半壁店任国明", "石家庄陈永刚", _
- "咸阳李现堂", "咸阳陈志国", "音乐园陈志国", "王林家刘希宾", "马驹桥工地", "双桥工地", _
- "音乐园东院水", "万子营及大鲁店水电", "音乐园东院电", _
- "黄渠", "任文平", "苏州", "土建转水电", "小汤山工地", "七棵树李现堂")
- For Each sht In Sheets(bm)
- With sht
- arr = .UsedRange
- For i = 4 To UBound(arr)
- For j = 3 To 14
- If arr(i, 2) <> Empty Then
- s = arr(i, 2)
- ss = arr(i, 2) & "|" & arr(2, j)
- d1(s) = ""
-
- d(ss) = d(ss) + Val(arr(i, j))
- End If
- Next
- Next
- End With
- Next
- ReDim zrr(1 To d.Count, 1 To 2)
- For Each k In d1.keys
- m = m + 1
- zrr(m, 1) = m
- zrr(m, 2) = k
- Next
- With Sheets("分月汇总")
- .[a3].Resize(10000, 15) = ""
- .[a3].Resize(m, 2) = zrr
- arr = .[a1].Resize(m + 2, 15)
- For i = 3 To UBound(arr)
- Sum = 0
- For j = 3 To 14
- s = arr(i, 2) & "|" & arr(2, j)
- arr(i, j) = d(s)
- Sum = Sum + arr(i, j)
- Next
- arr(i, 15) = Sum
- Next
- .[a1].Resize(m + 2, 15) = arr
- End With
- Set d = Nothing
- Set d1 = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|