如何用VBA实现跨工作表精准匹配员工ID与岗位代码53并返回布尔值

需求说明 #

• 数据源表Ach：A列存储员工ID，B列存储岗位职责代码（单个员工对应1-20个不同代码）
• 结果表Qualified：A列存储员工ID，需在C列标记该员工是否在Ach表中存在岗位代码53，返回True/False或直接显示53

完善后的VBA代码 #

Sub CheckJobCode53()
    Dim dataWs As Worksheet, outputWs As Worksheet
    Dim idDict As Object
    Dim lastRowData As Long, lastRowOutput As Long
    Dim i As Long, id As String
    
    ' 指定工作表对象
    Set dataWs = ThisWorkbook.Worksheets("Ach")
    Set outputWs = ThisWorkbook.Worksheets("Qualified")
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' 获取Ach表数据最后一行行号
    lastRowData = dataWs.Cells(dataWs.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Ach表，收集所有拥有代码53的员工ID
    For i = 2 To lastRowData ' 假设第1行为表头，从第2行开始遍历数据
        id = dataWs.Cells(i, "A").Value
        If dataWs.Cells(i, "B").Value = 53 Then
            ' 字典去重，同一员工只存一次
            If Not idDict.Exists(id) Then
                idDict.Add id, True
            End If
        End If
    Next i
    
    ' 获取Qualified表数据最后一行行号
    lastRowOutput = outputWs.Cells(outputWs.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Qualified表，标记检查结果
    For i = 2 To lastRowOutput ' 假设第1行为表头
        id = outputWs.Cells(i, "A").Value
        If idDict.Exists(id) Then
            outputWs.Cells(i, "C").Value = True ' 若要显示"53"，替换为：outputWs.Cells(i, "C").Value = "53"
        Else
            outputWs.Cells(i, "C").Value = False
        End If
    Next i
    
    ' 释放内存
    Set idDict = Nothing
    Set dataWs = Nothing
    Set outputWs = Nothing
    
    MsgBox "检查完成！"
End Sub

代码亮点 #

• 用Scripting.Dictionary存储符合条件的员工ID，避免重复判断，大幅提升3500行数据的处理效率
• 分两次遍历：先收集目标ID，再批量标记结果，逻辑清晰易维护
• 可灵活切换返回值：只需修改一行代码，就能在True/False和53文本之间切换

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