如何解决VBA宏执行后Excel表格其他列出现#NA错误

问题根源 #

1. 整行赋值破坏列默认状态：你用.ListRows.Add.Range = Array(...)直接给新增行的整行Range赋值，仅填充了前两列，其他列被强制置为未初始化状态——如果这些列原本有公式或依赖关系，就会返回#NA。
2. Match范围包含表头：Application.Match用了.ListColumns(1).Range，这个范围包含表格表头，可能导致匹配逻辑误判（比如把表头当成数据行），进而影响后续新增行的逻辑。

修正后的代码 #

Sub yo()
    Dim Letters As Variant, Chk As Variant, Ele As Range, i As Long
    Dim okTable As ListObject, newRow As ListRow
    
    ' 读取Sports表的运动选项
    Letters = Sheets("Sports").Range("C3:C5").Value
    ' 缓存OK表的列表对象，减少重复调用
    Set okTable = Sheets("OK").ListObjects(1)
    
    For Each Ele In Sheets("Students").ListObjects(1).ListColumns(1).DataBodyRange
        ' 先判断OK表是否有数据，避免空表时DataBodyRange报错
        If Not okTable.DataBodyRange Is Nothing Then
            ' 仅在数据区域匹配学生，排除表头
            Chk = Application.Match(Ele.Value, okTable.ListColumns(1).DataBodyRange, 0)
        Else
            ' 空表直接标记为未找到
            Chk = CVErr(xlErrNA)
        End If
        
        If IsError(Chk) Then
            For i = 1 To 3
                ' 新增行后，单独给指定列赋值，不干扰其他列
                Set newRow = okTable.ListRows.Add
                newRow.ListColumns(1).Range.Value = Ele.Value
                newRow.ListColumns(2).Range.Value = Letters(i, 1)
            Next i
        End If
    Next Ele
End Sub

关键修改说明 #

• 避免整行赋值：新增行后单独给第1、2列赋值，其他列会自动继承表格的默认设置（空白或预设公式），不会出现#NA。
• 修正Match范围：仅在DataBodyRange（数据行区域）内匹配学生，排除表头，确保匹配逻辑准确。
• 缓存列表对象：提前把OK表的ListObject赋值给变量okTable，减少重复调用工作表对象的开销，代码更高效。
• 空表判断：新增了OK表为空的判断，避免空表时调用DataBodyRange抛出错误。

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