Help with scanning barcodes into an excel invoice

NoSparks said:

@AD4GC
I had downloaded your file from that other site but can't get there now, can see there's been 2 responses, the last by ByteMarks, but can't open the thread.
Seeing that I did spend some time on this I'll post what I came up with
See if this does what you're after.

VBA Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to single cell change
If Target.CountLarge > 1 Then Exit Sub

Dim fndRng As Range, FindString As String
Dim arr As Variant

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' limit to specific range
If Not Intersect(Target, Range("A13:A44")) Is Nothing Then
    ' if changed to blank
    If Target.Value = "" Then
        ' move everything up to that row
        arr = Range(Cells(Target.Row + 1, 1), Cells(44, 5)).Value
        Target.Resize(44 - Target.Row, 5).Value = arr
        Range("A44").End(xlUp).Offset(1).Select
        GoTo Enablethings
    Else
    ' deal with the scan
        FindString = Target.Value
        ' if it already exist on invoice
        If WorksheetFunction.CountIf(Range("A13:A44"), FindString) = 2 Then
            'find the first one and add 1 to quantity
            Set fndRng = Range("A12:A44").Find(What:=FindString, _
                                            After:=Cells(12, 1), _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)
            If Not fndRng Is Nothing Then
                ' add 1 to existing quantity
                fndRng.Offset(, 4) = fndRng.Offset(, 4).Value + 1
                ' remove the current scan
                Target.Value = ""
                Target.Select
            End If
       
        Else    'add to invoice
            'check for scan in UPC-SKU column A
            With Sheets("UPC-SKU").Range("A:A")
                Set fndRng = .Find(What:=FindString, _
                                After:=.Cells(2, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not fndRng Is Nothing Then
                    Target.Offset(, 1).Resize(, 3).Value = fndRng.Offset(, 1).Resize(, 3).Value
                    Target.Offset(, 4) = 1
                    Target.Offset(1).Select
                Else
                    MsgBox FindString & "  was not found"
                    Target.Select
                End If
            End With
        End If
    End If
End If
   
    ' unhide another row as necessary
    If Target.Row > 17 And Target.Row < 43 Then
        Target.Resize(3).EntireRow.Hidden = False
    End If

Enablethings:
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

I can foresee an issue should a scan go overtop of an existing scan but for right now I'm not going to deal with that until I know what else is happening with this.

Click to expand...

AD4GC said:

That code does not work on my end.

Click to expand...

AD4GC said:

However, rpaulson did send me a solution. I'll let him decide whether to share the code or not, but thanks for the help.

Click to expand...