如何在VBA中优先匹配Employee ID，无匹配则匹配Position ID？

要实现优先匹配Employee ID，匹配失败则 fallback 到Position ID的逻辑完全可行，核心是通过错误处理捕获Match函数找不到匹配项的情况，再执行二次匹配。以下是修改后的Update按钮VBA代码，Delete按钮可复用相同匹配逻辑：

Private Sub BTN02_Update_Click()

If Me.TB01_EmployeeID = "" Then
    MsgBox "请选择要更新的记录。"
    Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Employee Data")
Dim Selected_Row As Variant ' 用Variant接收，避免匹配失败时报错

' 第一步：优先匹配Employee ID（A列）
On Error Resume Next ' 开启错误捕获，匹配失败时跳过报错
Selected_Row = Application.Match(Me.TB01_EmployeeID.Value, sh.Range("A:A"), 0)
On Error GoTo 0 ' 关闭错误捕获

' 第二步：如果Employee ID没匹配到，尝试匹配Position ID（E列）
If IsError(Selected_Row) Then
    On Error Resume Next
    Selected_Row = Application.Match(Me.TB04_PostitionID.Value, sh.Range("E:E"), 0)
    On Error GoTo 0
    
    ' 两次都没匹配到的情况
    If IsError(Selected_Row) Then
        MsgBox "未找到匹配的Employee ID或Position ID，请检查输入。"
        Exit Sub
    End If
End If

' 执行记录更新
sh.Range("A" & Selected_Row).Value = Me.TB01_EmployeeID
sh.Range("B" & Selected_Row).Value = Me.TB02_FirstName
sh.Range("C" & Selected_Row).Value = Me.TB03_LastName
sh.Range("D" & Selected_Row).Value = Me.CB01_Postition
sh.Range("E" & Selected_Row).Value = Me.TB04_PostitionID
sh.Range("F" & Selected_Row).Value = Me.TB05_HireDate
sh.Range("G" & Selected_Row).Value = Me.CB02_PayType
sh.Range("H" & Selected_Row).Value = Me.CB03_Status
sh.Range("I" & Selected_Row).Value = Me.CB04_Shift
sh.Range("J" & Selected_Row).Value = Me.CB05_Supervisor
sh.Range("K" & Selected_Row).Value = Me.CB06_Department
sh.Range("L" & Selected_Row).Value = Me.CB07_DepartmentCode
sh.Range("M" & Selected_Row).Value = Me.TB06_Birthday
sh.Range("N" & Selected_Row).Value = Me.CB08_ShirtSize
sh.Range("O" & Selected_Row).Value = Me.CB09_Veteran

' 清空表单
Me.TB01_EmployeeID.Value = ""
Me.TB02_FirstName.Value = ""
Me.TB03_LastName.Value = ""
Me.CB01_Postition.Value = ""
Me.TB04_PostitionID.Value = ""
Me.TB05_HireDate.Value = ""
Me.CB02_PayType.Value = ""
Me.CB03_Status.Value = ""
Me.CB04_Shift.Value = ""
Me.CB05_Supervisor.Value = ""
Me.CB06_Department.Value = ""
Me.CB07_DepartmentCode.Value = ""
Me.TB06_Birthday.Value = ""
Me.CB08_ShirtSize.Value = ""
Me.CB09_Veteran.Value = ""

MsgBox "记录已更新"

End Sub

关键逻辑说明 #

1. 错误捕获机制：用On Error Resume Next捕获Match函数匹配失败的错误，避免代码直接崩溃
2. 双条件优先级：先尝试匹配A列的Employee ID，返回错误值时再匹配E列的Position ID
3. 兜底判断：如果两次匹配都失败，弹出提示并终止流程
4. 数据类型适配：将Selected_Row定义为Variant，因为Application.Match匹配失败时会返回错误值，Long类型无法接收

注意事项 #

• 如果Position ID存在重复值，Match函数会返回第一个匹配项的行号，若需处理重复场景，需额外添加判断逻辑
• Delete按钮的修改逻辑完全一致，只需将更新单元格的代码替换为删除行的代码（如sh.Rows(Selected_Row).Delete）

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