CODEWITHSUNDEEP
vbaexcel
Christine Cordero
Christine Cordero

Reputation: 25

error message for values not found using Find function

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

Upvotes: 2

Views: 1798

Answers (3)

user4039065
user4039065

Reputation:

The worksheet's MATCH is as fast or faster than Range.Find within a single column. Using Application.Match returned to a variant allows you to test with IsError.

Private Sub CheckBarcodeStatusCommandButton_Click()
    dim m as variant, str as string

    with ActiveWorkbook.Sheets("Inventory Log")

        str = CheckBarcodeTextBox.Text
        m = application.match(str, .range("J:J"), 0)

        if not iserror(m) then
            If .cells(m, "K").Value = "In" Then
                MsgBox ("The barcode no. " & str & " is currently available")
            ElseIf .cells(m, "K").Value = "Out" Then
                MsgBox ("The barcode no. " & str & " has already been used.")
            End If
        else
            'maybe do something if not found
        end if

    end with

End Sub

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

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.

Upvotes: 3

PaichengWu
PaichengWu

Reputation: 2689

Try

Private Sub CheckBarcodeStatusCommandButton_Click()
    dim c as range

    ActiveWorkbook.Sheets("Inventory Log").Columns("J:J").Select
    on error resume next
    set c = Selection.Find(What:=CheckBarcodeTextBox.Text, after:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, _
        SearchFormat:=False)

    if c is nothing then 
        msgbox "barcode not found."
    else
        c.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
    end if
    on error goto 0
    Application.DisplayAlerts = False
End Sub

Upvotes: 1

Related Questions