VBA出现Object Variable or With Block Variable Not Set错误排查

错误原因分析 #

• 遍历所有工作表时，若某张表不存在“Cost Code”列，Range.Find会返回Nothing，此时直接调用FindNext就会触发Object Variable or With Block Variable Not Set错误。
• 单工作表测试时你选的是有目标列的表，所以没触发问题，但遍历全表时必然会遇到无目标列的场景。

修正后的完整代码 #

Sub ExtractCostCodes()
    Dim ws As Worksheet
    Dim targetWs As Worksheet
    Dim costCodeCol As Range
    Dim firstFound As Range
    Dim currentCell As Range
    Dim lastRow As Long
    
    ' 绑定目标工作表
    Set targetWs = ThisWorkbook.Worksheets("Vendor DATA")
    ' 清空目标表已有数据（保留表头）
    targetWs.Range("A2:" & targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Address).ClearContents
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过目标表本身，避免重复提取
        If ws.Name <> targetWs.Name Then
            ' 查找Cost Code列的表头
            Set costCodeCol = ws.Rows(1).Find(What:="Cost Code", LookIn:=xlValues, LookAt:=xlWhole)
            
            ' 确认找到目标列后再继续
            If Not costCodeCol Is Nothing Then
                ' 查找该列第一个非空单元格（从表头下一行开始）
                Set currentCell = ws.Cells(2, costCodeCol.Column).Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)
                
                If Not currentCell Is Nothing Then
                    Set firstFound = currentCell ' 记录首个匹配单元格，防止死循环
                    
                    Do
                        ' 获取目标表最后一行
                        lastRow = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row + 1
                        ' 复制数据到目标表
                        targetWs.Cells(lastRow, "A").Value = currentCell.Value
                        
                        ' 查找下一个非空单元格
                        Set currentCell = ws.Cells(, costCodeCol.Column).FindNext(currentCell)
                        
                    ' 回到首个单元格时退出循环
                    Loop While Not currentCell Is Nothing And currentCell.Address <> firstFound.Address
                End If
            End If
        End If
    Next ws
    
    MsgBox "数据提取完成！", vbInformation
End Sub

关键修复点 #

• 空对象校验：每次调用Find后，都用If Not ... Is Nothing确认找到目标，避免对空对象执行操作。
• 排除目标表：遍历过程中跳过“Vendor DATA”表，防止重复提取自身数据。
• 防死循环处理：用firstFound记录首个匹配单元格的地址，避免FindNext无限循环。
• 限定查找范围：将非空单元格的查找范围限定在“Cost Code”列内，避免跨列误查。

额外提示 #

• 如果表头不在第1行，把ws.Rows(1)改成对应的表头行号即可。
• 若需要复制格式或其他属性，可将.Value替换为.Copy+PasteSpecial操作，单纯提取数据用.Value效率更高。

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