level 7
level 9
level 13
以前写过类似的，非常麻烦，而且还有一堆问题，假如两个仓库，同时进了100，然后仓库1用完了，又马上进了100，后面是应该是用仓库2还是继续用仓库1？
2024年09月26日 07点09分
6
这个不用担心，就是按照对应仓库扣减和匹配，比如仓库1用完了，那也就不会有订单，如果后面仓库1进了100，后面有仓库1的订单就继续扣减仓库1的。这里暂时不存在混用，其实主要的难点就是怎么去判断各个仓库实时的库存，然后匹配对应的编号
2024年09月26日 07点09分
回复 筱布點是我 :所以还是太麻烦了，我之前写法是直接把每天多少库存全部列出来，做一个巨大的辅助表
2024年09月26日 08点09分
吧务
level 15
level 5
Sub FillWarehouseIDs()
Dim wsConsumption As Worksheet
Dim wsStock As Worksheet
Dim lastRowConsumption As Long
Dim lastRowStock As Long
Dim i As Long
Dim consumptionQty As Double
Dim stock As Object
Dim warehouseName As String
Dim warehouseID As Variant
' 设置工作表
On Error Resume Next ' 启用错误处理
Set wsConsumption = ThisWorkbook.Sheets("表一")
If wsConsumption Is Nothing Then
MsgBox "未找到工作表：表一"
Exit Sub
End If
Set wsStock = ThisWorkbook.Sheets("表二")
If wsStock Is Nothing Then
MsgBox "未找到工作表：表二"
Exit Sub
End If
On Error GoTo 0 ' 关闭错误处理
lastRowConsumption = wsConsumption.Cells(wsConsumption.Rows.Count, "B").End(xlUp).Row
lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
' 创建字典对象
Set stock = CreateObject("Scripting.Dictionary")
' 读取库存数据到字典，从第三行开始
For i = 3 To lastRowStock
If IsNumeric(wsStock.Cells(i, 1).Value) And IsNumeric(wsStock.Cells(i, 3).Value) Then
warehouseID = wsStock.Cells(i, 1).Value
Dim quantity As Double
quantity = wsStock.Cells(i, 3).Value
If Not stock.Exists(warehouseID) Then
stock.Add warehouseID, 0
End If
stock(warehouseID) = stock(warehouseID) + CDbl(quantity)
Else
Debug.Print "第 " & i & " 行: 仓库ID或数量不是数字"
End If
Next i
' 填入仓库序号和匹配消耗量，从第三行开始
For i = 3 To lastRowConsumption
warehouseName = wsConsumption.Cells(i, 2).Value ' 获取仓库名称
If IsEmpty(warehouseName) Then
Debug.Print "行 " & i & ": 仓库名称为空"
GoTo NextIteration
End If
If IsNumeric(wsConsumption.Cells(i, 3).Value) Then
consumptionQty = CDbl(wsConsumption.Cells(i, 3).Value)
If consumptionQty > 0 Then
Dim found As Boolean
found = False
' 循环所有字典的键
For Each warehouseID In stock.Keys
Debug.Print "检查仓库ID: " & warehouseID
' 检查仓库名称是否匹配
Dim currentWarehouseName As String
currentWarehouseName = wsStock.Cells(warehouseID + 2, 2).Value ' 注意这里的 +2
If currentWarehouseName = warehouseName Then
If stock(warehouseID) >= consumptionQty Then
wsConsumption.Cells(i, 1).Value = warehouseID ' 填入仓库序号
stock(warehouseID) = stock(warehouseID) - consumptionQty ' 更新库存
found = True
Debug.Print "填充行 " & i & " 的仓库序号为: " & warehouseID ' 输出调试信息
Exit For
End If
End If
Next warehouseID
If Not found Then
Debug.Print "行 " & i & ": 消耗量 " & consumptionQty & " 无法满足，库存不足！" ' 输出调试信息
End If
End If
Else
Debug.Print "行 " & i & ": 消耗量不是数字或为空"
End If
NextIteration:
Next i
MsgBox "仓库序号填充完成！"
End Sub
应该，好像，没有问题
2024年09月26日 09点09分
9
Dim wsConsumption As Worksheet
Dim wsStock As Worksheet
Dim lastRowConsumption As Long
Dim lastRowStock As Long
Dim i As Long
Dim consumptionQty As Double
Dim stock As Object
Dim warehouseName As String
Dim warehouseID As Variant
' 设置工作表
On Error Resume Next ' 启用错误处理
Set wsConsumption = ThisWorkbook.Sheets("表一")
If wsConsumption Is Nothing Then
MsgBox "未找到工作表：表一"
Exit Sub
End If
Set wsStock = ThisWorkbook.Sheets("表二")
If wsStock Is Nothing Then
MsgBox "未找到工作表：表二"
Exit Sub
End If
On Error GoTo 0 ' 关闭错误处理
lastRowConsumption = wsConsumption.Cells(wsConsumption.Rows.Count, "B").End(xlUp).Row
lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
' 创建字典对象
Set stock = CreateObject("Scripting.Dictionary")
' 读取库存数据到字典，从第三行开始
For i = 3 To lastRowStock
If IsNumeric(wsStock.Cells(i, 1).Value) And IsNumeric(wsStock.Cells(i, 3).Value) Then
warehouseID = wsStock.Cells(i, 1).Value
Dim quantity As Double
quantity = wsStock.Cells(i, 3).Value
If Not stock.Exists(warehouseID) Then
stock.Add warehouseID, 0
End If
stock(warehouseID) = stock(warehouseID) + CDbl(quantity)
Else
Debug.Print "第 " & i & " 行: 仓库ID或数量不是数字"
End If
Next i
' 填入仓库序号和匹配消耗量，从第三行开始
For i = 3 To lastRowConsumption
warehouseName = wsConsumption.Cells(i, 2).Value ' 获取仓库名称
If IsEmpty(warehouseName) Then
Debug.Print "行 " & i & ": 仓库名称为空"
GoTo NextIteration
End If
If IsNumeric(wsConsumption.Cells(i, 3).Value) Then
consumptionQty = CDbl(wsConsumption.Cells(i, 3).Value)
If consumptionQty > 0 Then
Dim found As Boolean
found = False
' 循环所有字典的键
For Each warehouseID In stock.Keys
Debug.Print "检查仓库ID: " & warehouseID
' 检查仓库名称是否匹配
Dim currentWarehouseName As String
currentWarehouseName = wsStock.Cells(warehouseID + 2, 2).Value ' 注意这里的 +2
If currentWarehouseName = warehouseName Then
If stock(warehouseID) >= consumptionQty Then
wsConsumption.Cells(i, 1).Value = warehouseID ' 填入仓库序号
stock(warehouseID) = stock(warehouseID) - consumptionQty ' 更新库存
found = True
Debug.Print "填充行 " & i & " 的仓库序号为: " & warehouseID ' 输出调试信息
Exit For
End If
End If
Next warehouseID
If Not found Then
Debug.Print "行 " & i & ": 消耗量 " & consumptionQty & " 无法满足，库存不足！" ' 输出调试信息
End If
End If
Else
Debug.Print "行 " & i & ": 消耗量不是数字或为空"
End If
NextIteration:
Next i
MsgBox "仓库序号填充完成！"
End Sub
应该，好像，没有问题
![[大拇指]](/static/emoticons/u5927u62c7u6307.png)
