|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
做个VBA点击更新库存按纽,看看效果。
- Sub test()
- Dim a, b, c, n, d As Object
- Set d = CreateObject("scripting.dictionary")
- a = Sheets("入库").Range("b5:c" & Sheets("入库").[b65536].End(xlUp).Row)
- b = Sheets("出库").Range("b5:c" & Sheets("出库").[b65536].End(xlUp).Row)
- ReDim c(1 To UBound(a) + UBound(b), 1 To 4)
- Sheets("库存").UsedRange.Offset(4, 0).ClearContents
-
- For i = 1 To UBound(a)
- If Not d.exists(a(i, 1)) Then
- n = n + 1
- d(a(i, 1)) = n
- c(n, 1) = a(i, 1)
- c(n, 2) = a(i, 2)
- Else
- c(d(a(i, 1)), 2) = c(d(a(i, 1)), 2) + a(i, 2)
- End If
- Next
- For i = 1 To UBound(b)
- If Not d.exists(b(i, 1)) Then
- n = n + 1
- d(b(i, 1)) = n
- c(n, 1) = b(i, 1)
- c(n, 3) = b(i, 2)
- Else
- c(d(b(i, 1)), 3) = c(d(b(i, 1)), 3) + b(i, 2)
- End If
- Next
- For i = 1 To d.Count
- c(i, 4) = Val(c(i, 2)) - Val(c(i, 3))
- Next
- Sheets("库存").[b5].Resize(d.Count, 4) = c
-
- End Sub
复制代码
|
|