Excel vba检查匹配函数是否返回NA - 宏崩溃：运行时错误1004“应用程序定义或对象定义的错误”

I use a Match function to find a specific row with data:

我使用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

From i = 2 to i=14 it works fine. However the string in .Cells(15, 1) excel can't find in a given range which results in Runtime Error 1004 “Application-defined or Object-defined error error. I tried to fix it by checking if it's #N/A first:

从i = 2到i = 14,它工作正常。但是.Cells(15,1)中的字符串excel在给定范围内找不到导致运行时错误1004“应用程序定义或对象定义的错误错误。我试着通过检查它是否是#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

It returns False for rows 2-14 and still crashed for the 15th.

对于行2-14,它返回False,并且仍然在第15行崩溃。

At the same time if I just calculate the Match manually in the sheet and then check if the result is #N/A it works fine:

同时,如果我只是在工作表中手动计算匹配,然后检查结果是否为#N / A,它可以正常工作:

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 个解决方案

#1

With Match you need to check before getting the result if it was successful. You do that by using If Not IsError(Application.Match...

使用Match,如果成功,您需要在获得结果之前进行检查。你可以通过使用If Not IsError(Application.Match ...

Modified Code

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

You need to test the return value of .Cells(i, 7).Value before you pass it to WorksheetFunction.Match. I'd also suggest using the VBA function IsError instead of WorksheetFunction.IsNA:

在将它传递给WorksheetFunction.Match之前,需要测试.Cells(i,7).Value的返回值。我还建议使用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

#1