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
Try with this:
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.