1. I scan barcodes all day in random orders
2. My scanner Dumps barcodes into Excel (Column B) at end of day
3. I want VBA to count how many times each different individual barcode was scanned
4. Then take those tallies to each barcodes (each one is referenced in a cell in Column F) and add them to their running totals in the correct cell (each has its own cell in Column H)

This isnt working and I'm not sure why...

Private Sub addButton_Click()

    Dim Barcode As Variant
    Dim Scanner As Variant
    Dim Inventory As Variant

    Scanner = Range("B3,B36").Value
    Inventory = Range("G3, G61").Value



    i = 3
    For Each Barcode In Scanner
        If Barcode.Value = Inventory.Cells(i, 7).Value Then
            Cells(i, 9) = Cells(i, 9)   1
            Exit For
        End If
    Next Barcode
    i = i   1

End Sub

CodePudding user response：

Your first problem is that some of your ranges are defined using a comma(,) instead of a colon(:).

When you define a range with a comma like this: Range("B3,B36"), it will give you the cell B3 and cell B36 instead of the rectangular range going from B3 to B36 which is written Range("B3:B36").

Also, your i = i 1 should be inside your loop.

CodePudding user response：

Your iteration needs to be put inside of the loop.

Private Sub addButton_Click()

    Dim Barcode As Variant
    Dim Scanner As Variant
    Dim Inventory As Variant

    Scanner = Range("B3,B36").Value
    Inventory = Range("G3, G61").Value



    i = 3
    For Each Barcode In Scanner
        If Barcode.Value = Inventory.Cells(i, 7).Value Then
            Cells(i, 9) = Cells(i, 9)   1
            Exit For
        End If
        i = i   1
    Next Barcode
    

End Sub

CodePudding user response：

You can use Match to check your inventory for each barcode:

Private Sub addButton_Click()

    Dim Barcode As Range, ws As Worksheet
    Dim Scans As Range, m
    Dim Inventory As Range
    
    Set ws = ActiveSheet               'or some specific sheet
    Set Scans = ws.Range("B3:B36")     'values from scanner
    Set Inventory = ws.Range("G3:H61") 'description has Col F?
    
    For Each Barcode In Scans.Cells
        'find the barcode in the inventory list
        m = Application.Match(Barcode.Value, Inventory, 0)
        If Not IsError(m) Then
            'got a match: increment value in ColH
            With Inventory.Cells(m).EntireRow.Columns("H")
                .Value = .Value   1
            End With
        Else
            'no match: what to do?
        End If
    Next Barcode

End Sub