|
请dsmch老师帮忙修改一下代码,怎样让新建的表不汇总,非常感谢!
- Sub Macro1()
- Dim arr, brr, d, d2, d3, i&, j&, s&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary") '材料大类
- Set d3 = CreateObject("scripting.dictionary")
- ReDim brr(1 To 6000, 1 To 12)
- [a4:l20000].Clear
- w = Array("10kV清册表", "配变清册表", "0.4kV清册表", "0.22kV清册表", "户表清册表")
- For i = 0 To UBound(w)
- arr = Sheets(w(i)).Range("a1").CurrentRegion
- For j = 4 To UBound(arr)
- If Not IsNumeric(arr(j, 1)) And Not d2.exists(arr(j, 2)) Then n = n + 1: d2(arr(j, 2)) = n
- If d2.exists(arr(j, 2)) Then fj = d2(arr(j, 2))
- zf = arr(j, 2) & "," & arr(j, 3) & "," & fj
- If Not d.exists(zf) Then
- s = s + 1
- d(zf) = s
- brr(s, 2) = arr(j, 2)
- brr(s, 3) = arr(j, 3)
- brr(s, 4) = arr(j, 4)
- brr(s, i + 5) = arr(j, 5)
- brr(s, 11) = arr(j, 9)
- brr(s, 12) = fj
- If Not d3.exists(fj) Then
- n2 = n2 + 1: d3(fj) = 0
- brr(s, 1) = Replace(Application.Text(n2, "[dbnum1]"), "一十", "十")
- Else
- d3(fj) = d3(fj) + 1
- brr(s, 1) = d3(fj)
- End If
- Else
- s2 = d(zf)
- brr(s2, i + 5) = brr(s2, i + 5) + arr(j, 5)
- End If
- Next
- Next
- '合计
- For i = 1 To s
- hj = 0
- For j = 5 To 9
- hj = hj + brr(i, j)
- Next
- brr(i, 10) = hj
- Next
- With Range("a4").Resize(s, 12)
- .Value = brr
- .Sort [l4], Header:=xlGuess
- .Borders.LineStyle = xlContinuous
- End With
- [l:l].Clear
- End Sub
复制代码
|
|