使用VBA匹配Sheet1与Sheet2列A值，匹配成功更新Sheet2列B为Yes

问题说明 #

需实现：将Sheet1列A的每个值与Sheet2列A的值匹配，若Sheet2中存在对应值，则将该行的列B更新为"Yes"。以下是完善后的完整代码及关键逻辑说明：

完整代码 #

Sub UpdateStatus()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim list() As Variant
    Dim item As Long
    Dim matchRow As Variant
    
    ' 指定目标工作表，避免依赖当前活动表
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' 将Sheet1的A列数据存入数组，提升读取效率
    list = ws1.Range("A1:A" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row).Value
    
    ' 遍历Sheet1的每个值
    For item = 1 To UBound(list)
        ' 使用Match函数在Sheet2的A列快速查找匹配项
        matchRow = Application.Match(list(item, 1), ws2.Range("A:A"), 0)
        
        ' 判断是否找到匹配（非错误值即为找到）
        If Not IsError(matchRow) Then
            ' 更新Sheet2对应行的B列为"Yes"
            ws2.Cells(matchRow, "B").Value = "Yes"
        End If
    Next item
End Sub

关键逻辑说明 #

• 明确工作表：通过Set语句指定操作的Sheet1和Sheet2，避免因当前活动表切换导致代码执行出错。
• 数组存储数据：将Sheet1的A列数据一次性存入数组，比逐行读取单元格的方式更高效，数据量越大优势越明显。
• 匹配与更新：利用Application.Match函数快速定位匹配行号，若找到则直接修改Sheet2对应行的B列值；未找到则跳过该行。

大数据量优化方案 #

如果Sheet2数据量较大，可将Sheet2的A、B列数据也存入数组，通过双层循环匹配后一次性写回工作表，进一步提升效率：

Sub UpdateStatus_Fast()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim list1() As Variant, list2() As Variant
    Dim i As Long, j As Long
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' 读取两个工作表的目标列数据到数组
    list1 = ws1.Range("A1:A" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row).Value
    list2 = ws2.Range("A1:B" & ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row).Value
    
    ' 初始化Sheet2的B列为空（可选，根据需求调整）
    For i = 1 To UBound(list2)
        list2(i, 2) = ""
    Next i
    
    ' 双层循环匹配数据
    For i = 1 To UBound(list1)
        For j = 1 To UBound(list2)
            If list1(i, 1) = list2(j, 1) Then
                list2(j, 2) = "Yes"
                Exit For ' 找到匹配后退出内层循环，减少无效遍历
            End If
        Next j
    Next i
    
    ' 将更新后的数组写回Sheet2
    ws2.Range("A1:B" & UBound(list2)).Value = list2
End Sub

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