my barcode search wont find my barcode in my worksheet. my vba search code is not working properly

Question

When I scan my item bar code in my input field (megBox) my for loop does not Find/Match my item.

It keeps saying "item not found".

It only finds this item(21603000815) and prints "Found value on row 1". (which in only one of the many items)

everything else returns:"item not found"

how can it find the other items?

Sub findIt()
    Dim i As Long
    Dim x As Integer, y As Integer, q As Integer
    Worksheets(3).Activate
    With Worksheets(3).Range("a1:d12") 'looking in sheet with all items
        x = 0
        Do While xforms <> -1
            xforms = Application.InputBox("Enter Barcode", xTitleId, "", Type:=1)
            Application.Visible = True

            For q = 1 To 500    ' Revise the 500 to include all of your values
                If Worksheets(3).Cells(q, 2).Value = xforms Or Worksheets(3).Cells(q, 2).Formula = xforms Then
                    MsgBox ("Found value on row " & q)
                    Application.Visible = True
                    GoTo skip
                Else
                    MsgBox ("item Not Found")
                    Exit Sub
                End If
            Next q
    skip:
            ' This MsgBox will only show if the loop completes with no success
            MsgBox ("yessssssssssss")


            Worksheets("Barcodes").Range("a1").Offset(y, 0).Value = xforms 'putting items/barcord in a seperate tad to check for dupliate items
            Set c = .Find(xforms, LookIn:=xlValues)
            c.Select
            i = ActiveCell.Row
            Rows(i).Select
            Selection.Copy Worksheets("Shop Lable Info").Range("a1").Offset(x, 0)
            Rows(i + 1).Select
            Selection.Copy Worksheets("Shop Lable Info").Range("a2").Offset(x, 0)
            x = x + 2
            y = y + 1
        Loop
    End With
End Sub

Despite your question I recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code. — Pᴇʜ, Nov 13 '18 at 15:43
On first glance - ignoring not declared variables your `For q` loop looks at the first cell in the loop (`B1`). If the value is found it says so and exits the loop, if it's not the value then a messagebox is displayed and the procedure exits. Just removing that `Exit Sub` would cause the loop to carry on. — Darren Bartrup-Cook, Nov 13 '18 at 16:32
Could you delete your original question as well please - can't vote to close it as it's already on hold. — Darren Bartrup-Cook, Nov 13 '18 at 17:00

score 0 · Accepted Answer · answered Nov 13 '18 at 16:58

I'm not 100% sure what all your code is meant to be doing. I hope the code below mirrors what you're expecting fairly closely.

You'll have to change the sheet names I've used and maybe change the last bit?

Public Sub FindIt()

    Dim xForms As Long
    Dim rSearch As Range
    Dim rFound As Range
    Dim sFirstAdd As String
    Dim rCopyRange As Range

    Dim xTitleID As String: xTitleID = "Title for InputBox"

    xForms = Application.InputBox("Enter Barcode", xTitleID, "", Type:=1)

    'Only continue if a number > 0 was entered in xForms.
    'Pressing Cancel sets xForms to 0.
    If xForms <> 0 Then

        'ThisWorkbook is the file containing this code.
        With ThisWorkbook.Worksheets("Sheet3")
            Set rSearch = .Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp)) 'Row B1:B500 in your code.
        End With

        'Look for the first occurrence.
        Set rFound = rSearch.Find(xForms, rSearch.Cells(1, 1), xlValues, xlWhole, , xlNext)

        If Not rFound Is Nothing Then
            Set rCopyRange = rFound
            sFirstAdd = rFound.Address
            'If a value was found then search for others.
            'Stop when the search wraps back to the top again.
            Do
                Set rFound = rSearch.FindNext(rFound)
                Set rCopyRange = Union(rCopyRange, rFound) 'Create a range from all the found values.
            Loop While rFound.Address <> sFirstAdd

            'Copy the found rows to the "Bar Codes" sheet.
            With ThisWorkbook.Worksheets("Bar Codes")
                rCopyRange.EntireRow.Copy Destination:=.Cells(.Rows.Count, 1).End(xlUp)
            End With

        End If
    End If

End Sub

my barcode search wont find my barcode in my worksheet. my vba search code is not working properly

1 Answers1