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
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
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
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
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