代码之家 › 专栏 › 技术社区 › Ans

Excel vba check if Match函数返回NA-宏崩溃:运行时错误1004应用程序定义或对象定义错误

excel-vba vba excel

0

Ans · 技术社区 · 7 年前

我用一个 Match 函数查找包含数据的特定行:

Sub test

With ThisWorkbook.Worksheets("hide_rows")
 For i = 2 To 15
   MsgBox (Application.WorksheetFunction.Match(.Cells(i, 1).Value, _
                ThisWorkbook.Worksheets("Sheet1").Range("A:A"), 0)) 
 Next
End With

End Sub

从i=2到i=14,工作正常。但是字符串 .Cells(15, 1) Runtime Error 1004 âApplication-defined or Object-defined error 错误。我试着通过检查它是否 #N/A 第一:

Sub test

With ThisWorkbook.Worksheets("hide_rows")
 For i = 2 To 15
   MsgBox (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(.Cells(i, 1).Value, _
                ThisWorkbook.Worksheets("Sheet1").Range("A:A")), 0)) 
 Next
End With

End Sub

False 第2-14排,第15排仍在坠毁。

同时如果我计算在工作表中手动输入,然后检查结果是否为它工作正常:

Sub test()

With ThisWorkbook.Worksheets("hide_rows")
 For i = 2 To 15
   MsgBox (Application.WorksheetFunction.IsNA(.Cells(i, 7).Value))
 Next
End With

End Sub

2 回复 | 直到 7 年前

1

Shai Rado 7 年前

与 Match If Not IsError(Application.Match...

修改后的代码

Sub test()

With ThisWorkbook.Worksheets("hide_rows")
    For i = 2 To 15
        If Not IsError(Application.Match(.Cells(i, 1).Value, _
            ThisWorkbook.Worksheets("Sheet1").Range("A:A"), 0)) Then

            MsgBox (Application.WorksheetFunction.Match(.Cells(i, 1).Value, _
                ThisWorkbook.Worksheets("Sheet1").Range("A:A"), 0))

        Else    ' Match failed
            MsgBox "Row " & i & " failed to find a match", vbCritical, "Error"
        End If
    Next
End With

End Sub

2

Comintern 7 年前

您需要测试 .Cells(i, 7).Value 之前 WorksheetFunction.Match . 我还建议使用VBA函数 IsError WorksheetFunction.IsNA :

With ThisWorkbook.Worksheets("hide_rows")
    For i = 2 To 15
        Dim result As Variant

        result = .Cells(i, 1).Value
        If IsError(result) Then
            'Do whatever
        Else
            MsgBox (Application.WorksheetFunction.Match(result, _
                    ThisWorkbook.Worksheets("Sheet1").Range("A:A"), 0))
        End If
    Next
End With