- Sub 汇总()
- Dim brr
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("明细表").Range("b1:r" & Sheets("明细表").[b65536].End(3).Row)
- dzb = Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17)
- With Sheets("汇总表") '不改变原有汇总表,只做更新
- n = .[n65536].End(3).Row - 1
- If n > 0 Then '表示原有汇总表有内容
- brr = .Range("b2:o" & n + UBound(arr))
- For i = 1 To UBound(brr)
- d(brr(i, 13)) = i
- brr(i, 7) = 0: brr(i, 9) = 0
- Next
- Else '表示原来是空表
- ReDim brr(1 To UBound(arr), 1 To 14)
- End If
- For i = 2 To UBound(arr)
- x = arr(i, 16) '以采购单号为唯一判断标记
- If Not d.exists(x) Then
- n = n + 1
- d(x) = n
- For j = 1 To 14: brr(n, j) = arr(i, dzb(j - 1)): Next
- Else
- p = d(x)
- brr(p, 7) = brr(p, 7) + arr(i, 10)
- brr(p, 9) = brr(p, 9) + arr(i, 12)
- End If
- Next
- .[b2].Resize(n, 14) = brr
- End With
- End Sub
复制代码 |