|
“收款登记表”同一套房有多行收款记录,用“汇总表”方式来汇总,如何编VBA代码,谢谢大师帮忙
- Sub 汇总()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.[a1].CurrentRegion
- For I = 2 To UBound(arr)
- x = arr(I, 2)
- If Len(x) > 0 Then d(x) = d(x) & "," & I
- Next
- Dim brr(1 To 1000, 1 To 31)
- For Each x In d.keys '对于每个房号
- xrr = Split(d(x), ",")
- n = n + 1 '新增一条记录
- I = xrr(1)
- brr(n, 1) = n
- For j = 2 To 11 '前11列固定内容
- brr(n, j) = arr(I, j)
- Next
- For k = 1 To UBound(xrr) '后4列一组收款情况
- I = xrr(k)
- brr(n, j) = arr(I, 12) '收款日期
- brr(n, j + 1) = arr(I, 13) '收款
- brr(n, j + 3) = arr(I, 18) '收据编号
- j = j + 4
- Next
- Next
- Sheet2.[a7:ae1006] = ""
- Sheet2.[a7].Resize(n, 31) = brr
- End Sub
复制代码
|
|