|
- Sub shuaxin()
- Dim ir%, arr, d, a%, brr(1 To 9999, 1 To 4), k%, m%
- Set d = CreateObject("scripting.dictionary")
- With Sheet1
- ir = .Range("b" & Cells.Rows.Count).End(xlUp).Row
- arr = .Range("b2:c" & ir).Value
- For k = 1 To UBound(arr, 1)
- If d.Exists(arr(k, 1)) = False Then
- a = a + 1
- d(arr(k, 1)) = a
- brr(a, 1) = arr(k, 1)
- brr(a, 2) = arr(k, 2)
- Else
- m = d.Item(arr(k, 1))
- brr(m, 2) = brr(m, 2) + arr(k, 2)
- End If
- Next k
- End With
- With Sheet2
- ir = .Range("a" & Cells.Rows.Count).End(xlUp).Row
- arr = .Range("a2:b" & ir).Value
- For k = 1 To UBound(arr, 1)
- If d.Exists(arr(k, 1)) = False Then
- a = a + 1
- d(arr(k, 1)) = a
- brr(a, 1) = arr(k, 1)
- brr(a, 3) = arr(k, 2)
- Else
- m = d.Item(arr(k, 1))
- brr(m, 3) = brr(m, 3) + arr(k, 2)
- End If
- Next k
- End With
- For k = 1 To a
- brr(k, 4) = brr(k, 2) - brr(k, 3)
- Next k
- Sheet3.Range("a2").Resize(a, 4) = brr
- MsgBox "刷新完成", , "如有疑问,详询群号OFFICE之家-2群 39212411 入群验证:EXCEL"
- End Sub
复制代码 |
|