|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub 仓库分配()
- Dim arr, brr, crr()
- Dim m, n, i, j, x, y, a, b, k, t
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("J3:M6")
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 3)) Then
- m = 1
- ReDim brr(1 To 3, 1 To m)
- Else
- brr = d(arr(i, 3))
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 3, 1 To m)
- End If
- For j = 1 To 2
- brr(j, m) = arr(i, j)
- Next
- brr(3, m) = arr(i, 4)
- d(arr(i, 3)) = brr
- Next
- k = d.keys
- t = d.items
- arr = Sheet1.Range("A3:D6")
- For i = 1 To UBound(arr)
- b = arr(i, 3)
- For x = 0 To d.Count - 1
- If arr(i, 2) = k(x) Then
- For j = 1 To UBound(t(x), 2)
- a = 0
- a = t(x)(3, j)
- If a > 0 Then
- If a >= b Then
- t(x)(3, j) = t(x)(3, j) - b
- n = n + 1
- ReDim Preserve crr(1 To 6, 1 To n)
- crr(1, n) = arr(i, 4)
- crr(2, n) = arr(i, 1)
- crr(3, n) = arr(i, 3)
- crr(4, n) = t(x)(1, j)
- crr(5, n) = t(x)(3, j)
- crr(6, n) = b
- For y = 1 To n - 1
- If crr(4, y) = crr(4, n) Then
- crr(5, y) = 0
- Exit For
- End If
- Next
- b = b - a
- GoTo line1
- ElseIf a < b Then
- t(x)(3, j) = 0
- n = n + 1
- ReDim Preserve crr(1 To 6, 1 To n)
- crr(1, n) = arr(i, 4)
- crr(2, n) = arr(i, 1)
- crr(3, n) = arr(i, 3)
- crr(4, n) = t(x)(1, j)
- crr(5, n) = t(x)(3, j)
- crr(6, n) = a
- For y = 1 To n - 1
- If crr(4, y) = crr(4, n) Then
- crr(5, y) = 0
- Exit For
- End If
- Next
- b = b - a
- If b = 0 Then GoTo line1
- End If
- End If
- Next
- End If
- Next
- line1:
- Next
- Sheet1.Range("J11:O20") = ""
- If n > 0 Then Sheet1.[J11].Resize(n, 6) = Application.Transpose(crr)
- Set d = Nothing
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|