CODEWITHSUNDEEP
vbaexcelinventoryinventory-management
user5018013
user5018013

Reputation: 11

Code for excel inventory to count returns

I'm not a VBA expert but I am working on a temp inventory control using excel with a barcode scanner. I am currently using the code below (which I took from here quantity macro excel for inventory) to add the qty on the worksheet, ex. barcodeA scanned 3x will automatically register as 3 pcs in my worksheet. I need a way to incorporate subtracting quantity as well. I'd like the ff conditions to apply:

Cell "A1" = scan cell to add qty to inventory
Cell "B1" = scan cell to remove qty from the inventory

Any advise on how to tweak the code? I've been trying to adjust for days but whatever I do just doesn't seem to work.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SCAN_CELL As String = "A1"
    Const RANGE_BC As String = "A5:A500"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 1)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        f.Offset(0, 1).Value = 1
    End If

    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True

    Target.Select

End Sub

Upvotes: 1

Views: 1229

Answers (2)

Tim Williams
Tim Williams

Reputation: 166156

@Kazimierz beat me to it, but posting this anyway...

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SCAN_PLUS_CELL As String = "A1"
    Const SCAN_MINUS_CELL As String = "B1"

    Const RANGE_BC As String = "A5:A500"
    Dim val, f As Range, rngCodes As Range, inc, addr

    If Target.Cells.Count > 1 Then Exit Sub

    Select Case Target.Address(False, False)
        Case SCAN_PLUS_CELL: inc = 1
        Case SCAN_MINUS_CELL: inc = -1
        Case Else: Exit Sub
    End Select

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 1)
            .Value = .Value + inc 'should really check for 0 when decrementing
        End With
    Else
        If inc = 1 Then
            Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
            f.Value = val
            f.Offset(0, 1).Value = 1
        Else
            MsgBox "Can't decrement inventory for '" & val & "': no match found!", _
                    vbExclamation
        End If
    End If

    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True

    Target.Select

End Sub

Upvotes: 3

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Try with this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SCAN_CELL As String = "A1"
    Const SCAN_CELL_REMOVE As String = "B1"
    Dim intAddRemoveExit As Integer
    Const RANGE_BC As String = "A5:A500"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then intAddRemoveExit = 1
    If Not Intersect(Target, Me.Range(SCAN_CELL_REMOVE)) Is Nothing Then intAddRemoveExit = -1
    If intAddRemoveExit = 0 Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 1)
            .Value = .Value + intAddRemoveExit
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        f.Offset(0, 1).Value = 1
    End If

    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True

    Target.Select

End Sub

Please keep in mind that this solution doesn't check if product amount is higher then zero before removing. So, amount could go below zero.

Upvotes: 1

Related Questions