如何在VBA中为VLOOKUP添加错误检查-解网

问：

我正在将一些 excel 公式移动到 Visual Basic 以节省一些文件空间。我正在努力的一个方面是添加一些错误检查。

我有一个公式（在第 23/W 列中）：=IF（ISERROR（VLOOKUP（W2， auditdata！A：D， 1， FALSE）），“房间未审核”， “房间审核”）your text

我已经重新编写为Visual Basic代码，但我正在努力添加“On Error”语句。我使用这些说明作为参考：https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement

实际上，公式有点复杂 - 我需要检查两列的值，如果两者都存在于我正在检查的表数组中，则返回“audited”，否则“not audited”。我正在简化问题，以便更好地理解。

在两种情况下可能会发生错误：如果选中的单元格为空，或者它具有其他数据表中不存在的值。VBA 错误是运行时错误“1004”：

到目前为止的代码是：

Sub CheckRoomAudited_Method()
    Dim AuditCheckCell As Range 'cell where the vlookup results are returned

    Dim TotaltoCheck As Long 'last row in the column to check
    TotaltoCheck = Worksheets("RoomUse").Range("A2").End(xlDown).Row 'using first column as some data missing in lookup range column
    
    Dim EndofAuditRange As Long
    EndofAuditRange = Worksheets("auditdata").Range("A1").End(xlDown).Row 'this in the TableArray that will be checked
    
    Dim AuditCheckColumn As Range 'column where the vlookup results are returned
    Set AuditCheckColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 23), Worksheets("RoomUse").Cells(TotaltoCheck, 23)) 'seemed long-winded to specify but could not get it to work otherwise     
    
    Dim VlookupValueColumn As Range 'need to check values in this column to see if they exist in other sheet
    Set VlookupValueColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 37), Worksheets("RoomUse").Cells(TotaltoCheck, 37))
    
    Dim TableArrayRange As Range 'TableArray that will be checked
    Set TableArrayRange = Worksheets("auditdata").Range(Worksheets("auditdata").Cells(1, 1), Worksheets("auditdata").Cells(EndofAuditRange, 4))
    
    For Each AuditCheckCell In AuditCheckColumn
        'loop through column number 37 ("AK:AK") and check whether each value exist in the other table/sheet
    'write the value if it exist or "room not audited" if it does not exist in column 23/W
        AuditCheckCell.Value = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
    Next AuditCheckCell
    
End Sub

VBA Excel-Formula 错误处理 vlookup

谢谢大家的建议。最后，我们得到了 FunThomas 的答案，尽管 @FaneDuru 的单行代码也完美无缺。达伦·巴特鲁普-库克（Darren Bartrup-Cook），谢谢你的结束......以建议结束 - 我以为它更多地用于更改对象的属性。此外，TableArrayRange 不是 Excel 表格 - 它是我第二个工作表中的单元格范围。我这样命名它，因为它类似于 vlookup 参考。罗里，我已经把你的建议和FunThomas的建议一起使用了。

答：

1赞 FunThomas 7/26/2023 #1

基本上有两种方法可以从 VBA 执行：第一种是您正在使用的方法（），另一种是 .VLookupWorksheetFunction.VLookupApplication.VLookup

基本上，它们做同样的事情，唯一的区别是错误处理。

WorksheetFunction.VLookup如果找不到值，则引发运行时错误。您需要使用 -Statements 自行捕获此错误：On Error

Dim myValue As Variant
myValue = "(not found)"  ' Or whatever value you want to put into the cell if not found
On Error Resume Next    
myValue = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
On Error Goto 0
AuditCheckCell.Value = myValue

Application.VLookup不会引发运行时错误。相反，如果未找到该值，它将返回错误值。当您将该值写入单元格时，该单元格将显示 - 这正是 Excel 公式中 VLookup-Function 返回的内容。在 VBA 中，您可以使用函数来检查这一点，或者更具体地说， .#N/AIsErrorIsNA

Dim myValue As Variant
myValue = Application.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
if IsNA(myValue) Then
    AuditCheckCell.Value = "(not found)" 
Else
    AuditCheckCell.Value = myValue
End If

谢谢@FunThomas的建议和解释。我不确定我是否完全理解它，但之前已经理解 Application 就像一个对象，因此可以使用 Application 函数，但在其他情况下 WorksheetFunctions 更合适。我已经使用了您建议的代码，它简单明了且有效。虽然我不得不添加应用程序。在伊斯娜面前。此外，我只需要知道是否找到值，而不是值本身，因此相应地更改了代码。

0赞 TehDrunkSailor 7/26/2023 #2

您可以将范围的默认值设置为“未找到”大小写，如果查找成功，则替换它。喜欢这个：

For Each AuditCheckCell In AuditCheckColumn
        'loop through column number 37 ("AK:AK") and check whether each value exist in the other table/sheet
    'write the value if it exist or "room not audited" if it does not exist in column 23/W

    ' Default value
    AuditCheckCell.Value = "room not audited"

   ' Try to lookup the value
    On Error Resume Next
    AuditCheckCell.Value = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
    On Error GoTo 0

Next AuditCheckCell

默认情况下，这会将范围的值设置为“房间未审核”。然后，我们忽略错误，并尝试查找。如果查找成功，则范围的值将替换为查找。如果查找抛出错误，我们继续下一行，即停止忽略错误。

如何在VBA中为VLOOKUP添加错误检查

How to add error-checking for VLOOKUP in VBA

评论

评论

评论