|
- Sub test()
- Dim r%, i%
- Dim arr, brr, hj(1 To 10)
- Dim d As Object
- Dim flg As Boolean
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("代码科目")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:g" & r)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- ReDim brr(1 To 13)
- brr(1) = arr(i, 1)
- brr(2) = arr(i, 2)
- brr(3) = arr(i, 3)
- brr(4) = arr(i, 6)
- brr(5) = arr(i, 7)
- d(arr(i, 1)) = brr
- End If
- Next
- End With
- With Worksheets("明细")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:l" & r)
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 2)) Then
- brr = d(arr(i, 2))
- brr(6) = brr(6) + arr(i, 10)
- brr(7) = brr(7) + arr(i, 12)
- brr(11) = brr(11) + arr(i, 9)
- brr(12) = brr(12) + arr(i, 11)
- d(arr(i, 2)) = brr
- End If
- Next
- End With
- With Worksheets("分1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:l" & r)
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 2)) Then
- brr = d(arr(i, 2))
- brr(8) = brr(8) + arr(i, 10)
- brr(9) = brr(9) + arr(i, 12)
- d(arr(i, 2)) = brr
- End If
- Next
- End With
- arr = Application.Transpose(Application.Transpose(d.items))
- For i = UBound(arr) - 1 To 1 Step -1
- bm = arr(i, 1)
- j = i + 1
- Do While arr(j, 1) Like bm & "*"
- If arr(j, 1) Like bm & "???" Then
- For k = 1 To 10
- hj(k) = hj(k) + arr(j, k + 3)
- Next
- flg = True
- End If
- j = j + 1
- If j > UBound(arr) Then
- Exit Do
- End If
- Loop
- If flg Then
- For k = 1 To 10
- arr(i, k + 3) = hj(k)
- Next
- flg = flase
- Erase hj
- End If
- Next
- With Worksheets("汇总")
- .UsedRange.Offset(1, 0).Clear
- .Columns(1).NumberFormatLocal = "@"
- .Range("a2").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End Sub
复制代码 |
|