|
发表于 2012-10-10 11:08
|
显示全部楼层
本楼为最佳答案
美斯特邦威 发表于 2012-10-10 09:23
附件上来了
谢谢
猜一个,试试这个吧,两张表的格式完全不同,对应都对应不好~~- Sub text()
- Dim arr(), brr(1 To 60000, 1 To 6), k As Integer, i As Integer, h As Integer
- Dim a As Object
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet2.Range("a3:l" & Sheet2.Range("a65536").End(3).Row).Value
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 5)) Then
- k = k + 1
- d(arr(i, 5)) = k
- brr(k, 1) = arr(i, 5)
- brr(k, 2) = arr(i, 7)
- brr(k, 4) = arr(i, 11)
- brr(k, 5) = 0
- brr(k, 6) = brr(k, 4) - brr(k, 5)
- Else
- h = d(arr(i, 5))
- brr(h, 4) = brr(h, 4) + arr(i, 11)
- brr(h, 6) = brr(h, 4) - brr(h, 5)
- End If
- Next
- arr = Sheet3.Range("a3:i" & Sheet3.Range("a65536").End(3).Row).Value
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 4)) Then
- h = d(arr(i, 4))
- brr(h, 5) = brr(h, 5) + arr(i, 8)
- brr(h, 6) = brr(h, 4) - brr(h, 5)
- Else
- k = k + 1
- d(arr(i, 4)) = k
- brr(k, 1) = arr(i, 4)
- brr(k, 2) = arr(i, 6)
- brr(k, 4) = 0
- brr(k, 5) = arr(i, 8)
- brr(k, 6) = brr(k, 4) - brr(k, 5)
- End If
- Next
- With Sheet1
- .Range("a3:f65536").ClearContents
- .Range("a3").Resize(d.Count, 6) = brr
- End With
- End Sub
复制代码 |
|