使用查找函数未找到值的错误消息

我使用此代码来检查条形码是否在数据库中，但每次条形码不在列表中时，它都会提示一条错误消息：

运行时错误 91：未设置对象变量或 With 块变量。

是否有一行我可以像消息框一样添加，表明输入的条形码无效。我知道这就是我所需要的，但显然，我不知道应该使用哪个函数来附带 IF 语句。有什么建议么？

如果有人可以建议使用 FOR 语句（如果要搜索批次），我也将不胜感激。 1111-1114

Private Sub CheckBarcodeStatusCommandButton_Click()
    ActiveWorkbook.Sheets("Inventory Log").Select
    Columns("J:J").Select
    Selection.Find(What:=CheckBarcodeTextBox.Text, after:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, _
        SearchFormat:=False).Activate

    If ActiveCell.Offset(0, 1).Value = "In" Then
        MsgBox ("The barcode no. " & CheckBarcodeTextBox.Text _
            & " is currently available")
    ElseIf ActiveCell.Offset(0, 1).Value = "Out" Then
        MsgBox ("The barcode no. " & CheckBarcodeTextBox.Text _
            & " has already been used.")
    End If

    Application.DisplayAlerts = False
End Sub

来自文档 https://learn.microsoft.com/office/vba/api/excel.range.find:

该方法返回Nothing如果没有找到匹配项。这.Find方法不会影响选择或活动单元格。

这演示了如何使用VBA.Find method:

这假设（根据您的示例）CheckBarcodeTextBox包含要在列中匹配的文本J，仅匹配“整个单元格”。

Private Sub CheckBarcodeStatusCommandButton_Click()
    Dim lookFor As String, lookIn As Range, found As Range

    lookFor = CheckBarcodeTextBox.Text
    Set lookIn = ThisWorkbook.Sheets("Inventory Log").Columns("J:J")
    Set found = lookIn.Find(lookFor, , xlValues, xlWhole) 'match whole cell value

    If found Is Nothing Then
        'not found
        MsgBox "No match for: " & lookFor
    Else
        'found
        MsgBox "Found: " & lookFor & vbLf & _
               " in cell: " & found.Address & vbLf & _
               " which contains: " & found.Value
    End If
End Sub

如果您只需要检查是否匹配exists（并且不需要知道location），那么上面的例子可以稍微简化一下。

Private Sub CheckBarcodeStatusCommandButton_Click()
    Dim lookIn As Range
    Set lookIn = ThisWorkbook.Sheets("Inventory Log").Columns("J")
    If lookIn.Find(CheckBarcodeTextBox, , xlValues, xlWhole) Is Nothing Then
        MsgBox "Not found:."    'do something if not found
    Else
        MsgBox "Found."         'do something if found
    End If
End Sub

在工作表公式中我会使用VLOOKUP or MATCH，可以使用调用Application.WorksheetFunction但两者都需要On Error处理不匹配的情况，所以.Find可能是最好的。

来自 Microsoft 文档的更多信息：

• Range.Find Method (Excel) https://learn.microsoft.com/office/vba/api/excel.range.find
• Application.DisplayAlerts财产 (Excel) https://learn.microsoft.com/office/vba/api/excel.application.displayalerts

Recommended Bookmark: Microsoft Documentation: Office VBA Reference https://learn.microsoft.com/office/vba
^{Use the sites' left sidebar to navigate to sections like VBA functions https://learn.microsoft.com/office/vba/language/reference/functions-visual-basic-for-applications, methods https://learn.microsoft.com/office/vba/language/reference/methods-visual-basic-for-applications and statements https://learn.microsoft.com/office/vba/language/reference/statements.}