|
本帖最后由 哈啊哈 于 2023-9-6 16:33 编辑
应退数量是已知需要退回的数量,
想把表格C列应退的数量,随机分配到对应每个仓的数量(库存足够足的情况下)。
在8大仓都有现货的情况下,可以随机选择退哪个仓的数量(无规则),请大神们帮助写vba代码呀。感谢感谢
- Sub demo林()
- Dim arr, brr, crr, h, n, sht1, sht2, i, j, a, b
- Set sht1 = Worksheets("导出文本")
- Set sht2 = Worksheets("操作2")
- With sht1
- h = .Range("A65535").End(xlUp).Row
- n = .Range("a1").End(xlToRight).Column
- arr = .Range("A1:C" & h)
- brr = .Range("D1").Resize(1, n - 3)
- End With
- With sht2
- .UsedRange.ClearContents
- .Range("A1").Resize(h, 3) = arr
- For i = 1 To n - 3
- .Range("D1:D" & h).Offset(0, j * 2) = sht1.Range("D1:D" & h).Offset(0, j).Value
- .Range("D1:D" & h).Offset(0, j * 2 + 1) = ""
- .Range("D1").Offset(0, j * 2 + 1) = VBA.Replace(sht1.Range("D1").Offset(0, j).Value, "现货库存", "下架数量")
- j = j + 1
- Next
- .Range("D1").Offset(0, j * 2).Resize(1, 3) = Array("总退货", "总剩余", "核对")
-
- For i = 2 To h
- If arr(i, 3) = 0 Or arr(i, 3) = "" Then
- Else
- a = 0
- For j = 4 To 2 * (n - 3) + 3
- If arr(i, 3) = .Range("D1").Offset(i - 1, j - 4).Value Then
- a = 1
- .Range("D1").Offset(i - 1, j - 4 + 1) = arr(i, 3)
- Exit For
- End If
- Next j
- If a = 0 Then
- For j = 4 To 2 * (n - 3) + 3
- b = Application.WorksheetFunction.Min(VBA.Val(arr(i, 3)), VBA.Val(.Range("D1").Offset(i - 1, j - 4).Value))
- .Range("D1").Offset(i - 1, j - 4 + 1) = b
- j = j + 1
- arr(i, 3) = arr(i, 3) - b
- If arr(i, 3) = 0 Then
- Exit For
- End If
- Next j
- End If
- End If
- Next
- End With
- End Sub
复制代码
|
|