|
试试看这结果对不?
- Sub 合计()
- Dim D As Object, Mo As Byte, H As Long
- Dim Hx As Long, Arr(), Brr()
- Set D = CreateObject("scripting.Dictionary") '创建一个字典
- With Sheets("Sheet2") '指定操作 Sheet2 表
- Mo = .Range("B1").Value '记录月
- Hx = .Range("A65536").End(xlUp).Row '提取使用行数
- .Range("B4:C" & Hx).ClearContents '清除结果
- Arr = .Range("A4:C" & Hx).Value '将数据区域交给数组
-
- For Hx = 1 To UBound(Arr)
- D(Arr(Hx, 1)) = Hx '循环,将科目所在行号交到字典中
- Next
-
- With Sheets("Sheet1") '操作 Sheet1 表,提取数据源
- Hx = .Range("A65536").End(xlUp).Row
- Brr = .Range("A2:D" & Hx)
- End With
-
- For Hx = 1 To UBound(Brr) '循环数据源
- If Brr(Hx, 1) = Mo Then '如果月 相同
- H = D.Item(Brr(Hx, 2)) '在 字典中 提取科目所在行
-
- Arr(H, 2) = Brr(Hx, 3) + Arr(H, 2) '借方累加
- Arr(H, 3) = Brr(Hx, 4) + Arr(H, 3) '贷方累加
- End If
- Next
-
- .Range("A4").Resize(UBound(Arr), 3) = Arr '将结果写回单元格
- End With
- End Sub
复制代码 |
|