使用“查找"功能找不到值的错误消息 [英] 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:
Recommended Bookmark: Microsoft Documentation: Office VBA Reference
Use the sites' left sidebar to navigate to sections like VBA functions, methods and statements.
这篇关于使用“查找"功能找不到值的错误消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!