|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Option Explicit
- Sub test()
- Const tbLeftColNum% = 9
- Const tbRightColNum% = 10
- Dim arr(), maxRowC%, maxRowL%, i&, m&, n&, leftQty&, rightQty&
- Dim dLeft As Object, dRight As Object, product
-
- Set dLeft = CreateObject("scripting.dictionary")
- Set dRight = CreateObject("scripting.dictionary")
-
- With Sheets("原表")
- maxRowC = .Cells(Rows.Count, "C").End(xlUp).Row
- maxRowL = .Cells(Rows.Count, "L").End(xlUp).Row
-
- For i = 3 To maxRowC
- product = .Cells(i, "C")
- If Not dLeft.exists(product) Then
- Set dLeft(product) = New Collection
- dLeft(product).Add .Cells(i, "A").Resize(1, tbLeftColNum).Value
- Else
- dLeft(product).Add .Cells(i, "A").Resize(1, tbLeftColNum).Value
- End If
- Next
-
- For i = 3 To maxRowL
- product = .Cells(i, "L")
- If Not dRight.exists(product) Then
- Set dRight(product) = New Collection
- dRight(product).Add .Cells(i, "J").Resize(1, tbRightColNum).Value
- Else
- dRight(product).Add .Cells(i, "J").Resize(1, tbRightColNum).Value
- End If
- Next
- End With
-
- With Sheets("需要的结果")
- .UsedRange.Offset(2).Clear
- m = 3: n = 3
- For Each product In dLeft.keys
- If dRight.exists(product) Then
- leftQty = 0: rightQty = 0
-
- For i = 1 To dLeft(product).Count
- .Cells(m, "A").Resize(1, tbLeftColNum) = dLeft(product)(i)
- leftQty = leftQty + dLeft(product)(i)(1, 6)
- m = m + 1
- Next
- dLeft.Remove product
-
- If dRight.exists(product) Then
- For i = 1 To dRight(product).Count
- .Cells(n, "J").Resize(1, tbRightColNum) = dRight(product)(i)
- rightQty = rightQty + dRight(product)(i)(1, 4)
- n = n + 1
- Next
- dRight.Remove product
- End If
-
- m = IIf(m > n, m, n) + 1: n = m
- With .Cells(n - 1, "R")
- .Value = rightQty - leftQty
- .Font.Bold = True
- .Interior.ColorIndex = 8
- End With
- End If
- Next
-
- For Each product In dLeft.keys
- leftQty = 0
- For i = 1 To dLeft(product).Count
- .Cells(n, "A").Resize(1, tbLeftColNum) = dLeft(product)(i)
- leftQty = leftQty + dLeft(product)(i)(1, 6)
- n = n + 1
- Next
- With .Cells(n, "R")
- .Value = -leftQty
- .Font.Bold = True
- .Interior.ColorIndex = 8
- End With
- n = n + 1
- Next
-
- For Each product In dRight.keys
- rightQty = 0
- For i = 1 To dRight(product).Count
- .Cells(n, "J").Resize(1, tbRightColNum) = dRight(product)(i)
- rightQty = rightQty + dRight(product)(i)(1, 4)
- n = n + 1
- Next
- With .Cells(n, "R")
- .Value = rightQty
- .Font.Bold = True
- .Interior.ColorIndex = 8
- End With
- n = n + 1
- Next
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|