VBA如何在表格底部求和行上方插入VLookup匹配的#N/A对应数据

原有代码问题梳理 #

• 遍历整列P:P会导致无意义遍历104万行空单元格，运行效率极低
• 直接用cl.Value = "#N/A"判断会报错：如果单元格是公式返回的#N/A错误值，直接比对值会触发类型不匹配错误
• 频繁调用Select方法选中工作表、单元格，会导致界面跳闪，且容易因用户手动选中其他单元格引发逻辑错误
• VLookup使用逻辑错误：你需要提取匹配行A列的值，直接取匹配单元格所在行的A列即可，无需调用查找函数

最终可运行代码 #

Sub ADDNA()
    Dim wsAnalysis As Worksheet, wsTemplate As Worksheet
    Dim lastRowP As Long, i As Long, templateLastRow As Long
    Dim clVal As Variant
    
    ' 绑定工作表，避免后续重复引用
    Set wsAnalysis = ThisWorkbook.Worksheets("Analysis")
    Set wsTemplate = ThisWorkbook.Worksheets("Template")
    
    ' 获取Analysis表P列最后一个有数据的行号，自动适配任意起始行
    lastRowP = wsAnalysis.Cells(wsAnalysis.Rows.Count, "P").End(xlUp).Row
    
    ' 遍历P列所有有效数据行
    For i = 1 To lastRowP
        clVal = wsAnalysis.Cells(i, "P").Value
        ' 同时兼容公式返回的#N/A错误和文本格式的"#N/A"
        If (IsError(clVal) And clVal = CVErr(xlErrNA)) Or CStr(clVal) = "#N/A" Then
            ' 找到Template表现有数据最后一行（跳过底部求和行）
            templateLastRow = wsTemplate.Range("A3").End(xlDown).Row
            ' 在最后一行数据下方插入新行
            wsTemplate.Rows(templateLastRow + 1).Insert Shift:=xlUp
            ' 填入对应A列的值
            wsTemplate.Cells(templateLastRow + 1, "A").Value = wsAnalysis.Cells(i, "A").Value
        End If
    Next i
    
    ' 清空对象释放内存
    Set wsAnalysis = Nothing
    Set wsTemplate = Nothing
End Sub

适配说明 #

• 代码会自动识别P列的有效数据范围，无论有效数据从第5行还是第7行起始都可以正常识别，无需手动修改参数
• 如果你的Template表表头不是从A3起始，只需要修改wsTemplate.Range("A3").End(xlDown).Row里的A3为你的表头实际结束行即可
• 代码默认将匹配到的A列值填入Template表的A列，如果需要填入其他列，修改Cells(templateLastRow + 1, "A")里的列号即可

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