VBA宏开发需求：匹配SheetB与SheetA并更新指定列数据

需求说明：

• 检查SKU Exceptions工作表（需求中的SheetB）的SKU和Store信息，在Data工作表（需求中的SheetA）中匹配对应行
• 匹配成功后，将Data表的STORE、PRODUCT_TYPE、CONCAT列，替换为SKU Exceptions表中对应蓝色列的内容
• 支持新增数据后循环批量处理

示例：
当Data表中存在SKU为95908352且Store为-1的行时：

• STORE列替换为SKU Exceptions表的Store(Autofill)列内容
• PRODUCT_TYPE列替换为SKU Exceptions表的Product Type (Autofill)列内容
• CONCAT列替换为SKU Exceptions表的Move to列内容

待完善的初始VBA代码 #

Dim wsD As Worksheet, wsE As Worksheet, r As Long
Dim rngData As Range, rngEx As Range, mH, mR, col As Long, hdr, v
Dim rngCopy As Range, lr As Long, addr

Set wsD = ThisWorkbook.Worksheets("Data")
Set wsE = ThisWorkbook.Worksheets("SKU Exceptions")

Set rngData = wsD.Range("A3:Z" & wsD.Cells(Rows.Count, "A").End(xlUp).Row)

For col = 1 To 3  'loop the columns on the exceptions sheet
    lr = wsE.Cells(Rows.Count, col).End(xlUp).Row
    If lr > 1 Then
        'get the values and find on "Data"
        hdr = Replace(wsE.Cells(1, col).Value, "Product Type (Move to) ", "")
        hdr2 = Replace(wsE.Cells(1, col).Value, "Store (Autofill) ", "")
        mH = Application.Match(hdr, rngData.Columns(17), 0) And Application.Match(hdr2, rngData.Columns(1), 0)

        If Not IsError(mH) Then 'matched both values?
            For r = 2 To lr
                v = wsE.Cells(r, col).Value
                mR = Application.Match(v, rngData.Columns(17), 0)
                If Not IsError(mR) Then
                    'Copy new values over
                Else
                    'value was not matched
                    MsgBox "Value '" & v & "' not found on Data sheet!"
                End If
            Next r
        Else
            'header not matched
            MsgBox "Header '" & hdr & "' not found on data sheet!"
        End If
    End If
Next col

MsgBox "Sku's have been reordered successfully!", vbInformation, "Reorder Sku's Macro"

完善后的完整VBA代码 #

Sub UpdateSKUData()
    Dim wsD As Worksheet, wsE As Worksheet
    Dim lastRowD As Long, lastRowE As Long
    Dim skuColD As Long, storeColD As Long, prodTypeColD As Long, concatColD As Long
    Dim skuColE As Long, storeAutoColE As Long, prodTypeAutoColE As Long, moveToColE As Long
    Dim r As Long, matchRow As Variant
    
    ' 绑定工作表对象
    Set wsD = ThisWorkbook.Worksheets("Data")
    Set wsE = ThisWorkbook.Worksheets("SKU Exceptions")
    
    ' 匹配Data表所需列的位置（通过表头查找，避免固定列号失效）
    On Error Resume Next
    skuColD = wsD.Rows(2).Find("SKU", LookIn:=xlValues, LookAt:=xlWhole).Column
    storeColD = wsD.Rows(2).Find("STORE", LookIn:=xlValues, LookAt:=xlWhole).Column
    prodTypeColD = wsD.Rows(2).Find("PRODUCT_TYPE", LookIn:=xlValues, LookAt:=xlWhole).Column
    concatColD = wsD.Rows(2).Find("CONCAT", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    
    ' 检查Data表必填列是否存在
    If skuColD = 0 Or storeColD = 0 Or prodTypeColD = 0 Or concatColD = 0 Then
        MsgBox "Data表缺失必要表头，请检查SKU、STORE、PRODUCT_TYPE、CONCAT列", vbCritical
        Exit Sub
    End If
    
    ' 匹配SKU Exceptions表所需列的位置
    On Error Resume Next
    skuColE = wsE.Rows(1).Find("SKU", LookIn:=xlValues, LookAt:=xlWhole).Column
    storeAutoColE = wsE.Rows(1).Find("Store(Autofill)", LookIn:=xlValues, LookAt:=xlWhole).Column
    prodTypeAutoColE = wsE.Rows(1).Find("Product Type (Autofill)", LookIn:=xlValues, LookAt:=xlWhole).Column
    moveToColE = wsE.Rows(1).Find("Move to", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    
    ' 检查SKU Exceptions表必填列是否存在
    If skuColE = 0 Or storeAutoColE = 0 Or prodTypeAutoColE = 0 Or moveToColE = 0 Then
        MsgBox "SKU Exceptions表缺失必要表头，请检查SKU、Store(Autofill)等列", vbCritical
        Exit Sub
    End If
    
    ' 获取两表最后一行，支持新增数据批量处理
    lastRowD = wsD.Cells(Rows.Count, skuColD).End(xlUp).Row
    lastRowE = wsE.Cells(Rows.Count, skuColE).End(xlUp).Row
    
    ' 循环处理SKU Exceptions表的每一行数据
    For r = 2 To lastRowE
        Dim currentSKU As Variant, currentStore As Variant
        currentSKU = wsE.Cells(r, skuColE).Value
        currentStore = wsE.Cells(r, storeAutoColE).Value
        
        ' 组合SKU+Store作为匹配条件，确保行唯一匹配
        matchRow = Application.Match(currentSKU & "|" & currentStore, _
                    wsD.Range(wsD.Cells(3, skuColD), wsD.Cells(lastRowD, skuColD)) & "|" & _
                    wsD.Range(wsD.Cells(3, storeColD), wsD.Cells(lastRowD, storeColD)), 0)
        
        If Not IsError(matchRow) Then
            ' 匹配成功，更新对应列内容
            wsD.Cells(matchRow + 2, storeColD).Value = wsE.Cells(r, storeAutoColE).Value
            wsD.Cells(matchRow + 2, prodTypeColD).Value = wsE.Cells(r, prodTypeAutoColE).Value
            wsD.Cells(matchRow + 2, concatColD).Value = wsE.Cells(r, moveToColE).Value
        Else
            ' 匹配失败，提示具体信息
            MsgBox "SKU: " & currentSKU & " Store: " & currentStore & " 在Data表未找到匹配行", vbExclamation
        End If
    Next r
    
    MsgBox "数据更新完成！", vbInformation, "操作完成"
End Sub

关键改进说明：

• 采用表头动态匹配列位置，避免固定列号导致的维护问题
• 增加必填列存在性检查，提前拦截错误场景
• 使用SKU|Store组合匹配逻辑，确保唯一行匹配
• 自动识别两表最后一行，兼容新增数据的循环处理
• 匹配失败时明确提示具体SKU和Store信息，便于排查

内容的提问来源于stack exchange，提问作者Dane Cameron