|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
arr = Sheets("入库明细表").UsedRange
For j = 2 To UBound(arr)
If Not d.exists(arr(j, 4)) Then
Set d(arr(j, 4)) = CreateObject("scripting.dictionary")
End If
d(arr(j, 4))(j) = j
Next j
brr = Sheets("出库明细表").UsedRange
For j = 2 To UBound(brr)
If d.exists(brr(j, 5)) Then
For Each k In d(brr(j, 5)).keys
r = d(brr(j, 5))(k)
If arr(r, 5) > 0 Then
mn = WorksheetFunction.Min(brr(j, 6), arr(r, 5))
brr(j, 7) = brr(j, 7) + mn * arr(r, 6)
brr(j, 6) = brr(j, 6) - mn
arr(r, 5) = arr(r, 5) - mn
If brr(j, 6) = 0 Then Exit For
End If
Next k
End If
Next j
[g1].Resize(UBound(brr)) = WorksheetFunction.Index(brr, 0, 7)
End Sub
|
|