VBA库存表批量更新求助：现有代码陷入死循环无法正常执行

原代码问题分析 #

• 死循环根源：内层Do While循环中，找到匹配条码后未执行b = b + 1，导致b值固定，循环无法终止。
• 逻辑嵌套错误：内层循环的Loop与If语句位置混乱，导致仅处理第一个扫描条目就陷入死循环；外层Do While与内部If的判断重复冗余。
• 效率短板：逐行遍历固定行数的库存表匹配条码，数据量增大时性能急剧下降。

修复基础版代码 #

先修正语法与逻辑错误，确保功能正常运行：

Sub Inventory_Update_Basic()
    Dim i As Integer
    Dim b As Integer
    Dim scanSheet As Worksheet
    Dim inventorySheet As Worksheet
    
    ' 替换为实际工作表名称
    Set scanSheet = ThisWorkbook.Worksheets("条码扫描表")
    Set inventorySheet = ThisWorkbook.Worksheets("库存数据")
    
    i = 2
    ' 遍历扫描表D列非空行
    Do While scanSheet.Cells(i, "D").Value <> ""
        b = 2 ' 假设库存表第2行开始为数据行
        ' 动态获取库存表最后一行，替代固定行数
        Do While b <= inventorySheet.Cells(inventorySheet.Rows.Count, "B").End(xlUp).Row
            If inventorySheet.Cells(b, "B").Value = scanSheet.Cells(i, "D").Value Then
                inventorySheet.Cells(b, "C").Value = inventorySheet.Cells(b, "C").Value + scanSheet.Cells(i, "F").Value
                Exit Do ' 找到匹配后立即退出内层循环
            End If
            b = b + 1
        Loop
        i = i + 1
    Loop
End Sub

高效优化版（字典实现） #

针对大数据量场景，使用Scripting.Dictionary实现O(1)时间复杂度的查找，大幅提升效率：

Sub Inventory_Update_Efficient()
    Dim scanSheet As Worksheet
    Dim inventorySheet As Worksheet
    Dim inventoryDict As Object
    Dim lastScanRow As Long
    Dim lastInventoryRow As Long
    Dim i As Long
    Dim barcode As String
    Dim qty As Double
    
    Set scanSheet = ThisWorkbook.Worksheets("条码扫描表")
    Set inventorySheet = ThisWorkbook.Worksheets("库存数据")
    Set inventoryDict = CreateObject("Scripting.Dictionary")
    
    ' 1. 将库存条码与对应行号存入字典
    lastInventoryRow = inventorySheet.Cells(inventorySheet.Rows.Count, "B").End(xlUp).Row
    For i = 2 To lastInventoryRow
        barcode = inventorySheet.Cells(i, "B").Value
        If barcode <> "" Then
            inventoryDict(barcode) = i
        End If
    Next i
    
    ' 2. 遍历扫描表批量更新库存
    lastScanRow = scanSheet.Cells(scanSheet.Rows.Count, "D").End(xlUp).Row
    For i = 2 To lastScanRow
        barcode = scanSheet.Cells(i, "D").Value
        qty = scanSheet.Cells(i, "F").Value
        If inventoryDict.Exists(barcode) Then
            inventorySheet.Cells(inventoryDict(barcode), "C").Value = _
                inventorySheet.Cells(inventoryDict(barcode), "C").Value + qty
        End If
    Next i
    
    ' 释放对象
    Set inventoryDict = Nothing
    Set scanSheet = Nothing
    Set inventorySheet = Nothing
End Sub

核心优化点 #

• 明确指定工作表：避免依赖默认活动工作表，防止切换工作表时出错。
• 动态获取数据边界：不再使用固定行数，自动适配库存与扫描数据的增减。
• 字典快速匹配：提前缓存库存条码的位置，扫描时直接定位，避免逐行遍历。
• 终止冗余遍历：找到匹配条目后立即退出内层循环，减少无效运算。

内容的提问来源于stack exchange，提问作者D. Young