Excel库存文件与PO文件数量比对VBA代码优化需求

核心思路 #

用字典存储库存文件的Item code与对应Qty的映射，高效完成两个判断：

• 遍历PO文件每行，若Item code不在库存字典中，整行红色高亮
• 若Item code存在但库存Qty小于PO的Qty，整行黄色高亮（可自定义颜色）

完整可运行VBA代码 #

Sub CompareAndHighlight()
    Dim poWB As Workbook, invWB As Workbook
    Dim poWS As Worksheet, invWS As Worksheet
    Dim invDict As Object
    Dim lastRowPO As Long, lastRowInv As Long
    Dim i As Long
    Dim itemCode As String
    Dim poQty As Double, invQty As Double
    
    ' 创建字典对象用于快速查找
    Set invDict = CreateObject("Scripting.Dictionary")
    
    ' 打开库存文件（替换为你的实际文件路径）
    Set invWB = Workbooks.Open("C:\YourFolder\Inventory File.xlsx")
    Set invWS = invWB.Sheets("Sheet1") ' 若工作表名不是Sheet1，自行修改
    
    ' 读取库存文件数据到字典：Key为Item code，Value对应Qty
    lastRowInv = invWS.Cells(invWS.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRowInv ' 假设第1行是表头
        itemCode = Trim(invWS.Cells(i, "A").Value) ' Item code在A列
        invQty = CDbl(invWS.Cells(i, "C").Value) ' Qty在C列
        If Not invDict.Exists(itemCode) Then
            invDict.Add itemCode, invQty
        End If
    Next i
    
    ' 定位PO文件（代码需在PO文件中运行）
    Set poWB = ThisWorkbook
    Set poWS = poWB.Sheets("Sheet1") ' 若工作表名不是Sheet1，自行修改
    
    ' 清除PO表原有高亮格式
    poWS.Cells.Interior.ColorIndex = xlNone
    
    ' 遍历PO文件数据行，执行判断与高亮
    lastRowPO = poWS.Cells(poWS.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRowPO ' 假设第1行是表头
        itemCode = Trim(poWS.Cells(i, "A").Value)
        poQty = CDbl(poWS.Cells(i, "C").Value)
        
        ' 情况1：PO条目不在库存中，红色高亮整行
        If Not invDict.Exists(itemCode) Then
            poWS.Rows(i).Interior.Color = RGB(255, 0, 0)
        Else
            ' 情况2：库存Qty小于PO的Qty，黄色高亮整行
            invQty = invDict(itemCode)
            If invQty < poQty Then
                poWS.Rows(i).Interior.Color = RGB(255, 255, 0) ' 可替换为其他颜色RGB值
            End If
        End If
    Next i
    
    ' 关闭库存文件（不保存）
    invWB.Close SaveChanges:=False
    
    ' 释放内存对象
    Set invDict = Nothing
    Set poWS = Nothing
    Set invWS = Nothing
    Set poWB = Nothing
    Set invWB = Nothing
    
    MsgBox "比对高亮完成！"
End Sub

使用步骤 #

1. 打开PO File.xlsx，按Alt+F11打开VBA编辑器
2. 插入模块：右键左侧工程窗口的PO文件 → 插入 → 模块
3. 粘贴上述代码，修改库存文件路径、工作表名（若需调整）
4. 按F5运行代码，或在Excel中添加按钮绑定该宏

关键细节调整 #

• 列位置：若Item code不在A列、Qty不在C列，修改代码中Cells(i, "X")的列标识
• 颜色自定义：替换RGB()参数即可，比如橙色用RGB(255,165,0)，蓝色用RGB(0,0,255)
• 大数据适配：字典查找比嵌套循环快数倍，适合上万行数据的表

内容的提问来源于stack exchange，提问作者Amir Shahzad