excel - 如何修复宏在#value之后仍然继续

Question

我创建了一个宏，如果单元格为空白，我将“N/A”放在空单元格中。我通过多个工作表来做到这一点。我遇到的问题是一些工作表中有导致“#value！”的公式。然后导致我的宏出错。无论如何，我尝试了一些东西，但它不起作用。

Sub test()

Dim i As Integer
Dim r As Long, c As Long
'Dim ws As Worksheet

Application.ScreenUpdating = False
For i = 6 To Worksheets.Count


    For c = 1 To 14
    For r = 5 To 1000
        If Sheets(i).Cells(r, c) = "" Or "#value!" Then
        Sheets(i).Cells(r, c).Value = "N/A"
        End If

    Next r
    Next c
Next i

结束子

标签： excelvba

Answer 1

首先测试错误：

IF iserror(Sheets(i).Cells(r, c)) Then
    Sheets(i).Cells(r, c).Value = "N/A"
ElseIF Sheets(i).Cells(r, c) = "" Then
    Sheets(i).Cells(r, c).Value = "N/A"
End If