CODEWITHSUNDEEP
excelvba
Ken Bishop
Ken Bishop

Reputation: 1

Tying to search excel for number using

So I’m very new to this code writing and I’m trying to search a excel sheet for a number that is inputed from a barcode and found in a row then selecting another row to input my on hand quantities bar code is in row g the row I want to end up in is F. This is what I have and it doesn’t work. Please beware this is my fist attempt at any code writing.

Sub Barcodesearch()
'
' Barcodesearch Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Range("G3:G344").Select
    Selection.Find What:= (Inputbox "Please scan a barcode and hit enter if needed"), After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("F").Offset (0, 2)
End Sub

Upvotes: 0

Views: 62

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

You need to handle the case where no match was made:

Sub Barcodesearch()

    Dim bc, f As Range, ws As Worksheet
    
    Set ws = ActiveSheet
    
    bc = Trim(InputBox("Please scan a barcode and hit enter if needed"))
    If Len(bc) = 0 Then Exit Sub 'no entry was made

    Set f = ws.Range("G3:G344").Find(What:=bc, LookIn:=xlFormulas, _
                                     LookAt:=xlWhole, MatchCase:=False)
    If Not f Is Nothing Then
        f.Offset(0, -1).Select                   'got a match
    Else
        MsgBox "Barcode '" & bc & "' not found"  'no match
    End If

End Sub

Upvotes: 2

Related Questions