使用“查找"功能找不到值的错误消息 [英] error message for values not found using Find function

问题描述

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

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

我是否可以添加一行，例如msgbox，所以输入的条形码无效.我知道这是我所需要的，但是显然，我不知道我应该使用哪个函数来处理IF语句.有什么建议吗?

如果有人可以建议使用FOR语句，如果要搜索一批，请说不"，我也将不胜感激.1111-1114

 私有子CheckBarcodeStatusCommandButton_Click()ActiveWorkbook.Sheets(库存日志").选择列("J:J").选择Selection.Find(What:= CheckBarcodeTextBox.Text，之后:= ActiveCell，_LookIn:= xlFormulas，LookAt:= xlWhole，SearchOrder:= xlByRows，_SearchDirection:= xlNext，MatchCase:= False，MatchByte:= False，_SearchFormat:= False).激活如果ActiveCell.Offset(0，1).Value ="In"然后MsgBox(条形码编号."& CheckBarcodeTextBox.Text _&当前可用")ElseIf ActiveCell.Offset(0，1).Value ="Out"然后MsgBox(条形码编号."& CheckBarcodeTextBox.Text _&已被使用.")万一Application.DisplayAlerts = False结束子 

解决方案

来自
^{使用网站的左侧边栏导航至VBA之类的部分功能，方法和声明.}

I am using this code to check if a barcode is on the database but every time the barcode is not on the list it prompts an error message saying:

Runtime Error 91 : Object variable or With block variable not set.

Is there a line I can add like a msgbox that the barcode entered is invalid. I understand that this is what I need but apparently, I don't know which function I should use to come with an IF statement. Any suggestions?

I would also appreciate if anyone can suggest using the FOR statement if a batch would be searched say nos. 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

解决方案

From the documentation:

This method returns Nothing if no match is found. The .Find method does not affect the selection or the active cell.

---

This demonstrates how to use the VBA .Find method:

This assumes (based on your example) that CheckBarcodeTextBox contains text to match in Column J, matching the "entire cell" only.

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

If you only need to check if a match exists (and don't need to know the location of the match), then the above example can be simplified a bit.

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

In a worksheet formula I'd use VLOOKUP or MATCH, which can be called using Application.WorksheetFunction but both requires On Error handling to deal with non-matches, so .Find is probably best.

---

More Information from Microsoft Docs:

• Range.Find Method (Excel)
• Application.DisplayAlerts Property (Excel)

Recommended Bookmark: Microsoft Documentation: Office VBA Reference
^{Use the sites' left sidebar to navigate to sections like VBA functions, methods and statements.}

这篇关于使用“查找"功能找不到值的错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！