How To Auto Subtract From QTY Cell When Populating Another Cell

[LostInCodeLand]

Hello!

I'm a self-taught Excel novice trying to create an inventory system, and would greatly appreciate any help from The Masters

I'm using Excel 2007 to create the system for an Auto Shop, which stores many multiples of the same part/s but quickly depletes stock. Basically, I'd like to be able to scan my barcode/s every time an item is removed from the shelf and have the "Current Quantity" cell (in the same row on my worksheet) reduced by one (1).

I've got my barcode scanner populating Cell G (with the barcoded part info), but I can't figure out a formula that will auto reduce Cell F (current qty) by a count of 1 when G is populated.

I've been doing my best to research on my own, but I'm afraid my novice-ness is keeping me from recognizing the answer. If anyone could point me in the right direction, I'd very much appreciate the help. Thank you!

 

[mjbeam]

Hello!

G is a column, not a cell. Which cell is populated by the barcode scanner? F is a column also. Which cell needs to be reduced by 1 when the barcode scanner populates the cell?

 

[JLGWhiz]

If you want to try worksheet event code, the code below will do what you want, plus it will notify you if there is insufficient stock on record for the issue being made.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        If Target <> "" And Target.Offset(, -1) > 0 Then
            Target.Offset(, -1) = Target.Offset(, -1).Value - 1
        Else
            Beep
            MsgBox "Error"
        End If
    End If
Application.EnableEvents = True
End Sub

To install the code, right click the sheet name tab for the sheet where you scan the barcode to. Click 'View Code' in the pop up menu that appears. Copy and paste the above code into the code pane of the vb editor window and close the vb editor. Make sure your workbook is saved as a macro enabled workbook (.xlsm) to preserve the code. The procedure will now run whenever a change is made to your worksheet, but will only execute if the change occurs in column G. If you already have a worksheet change macro for that sheet, then this macro would need to be merged with the other one, so post back with the existing one for more assistance if needed.

 

[LostInCodeLand]

mjbeam - Sorry for the typo I'm trying to subtract 1 from cell F3 when cell G3 is populated. Thanks!

JLGWhiz - Thank you so much! I'll try that asap and post back with results.

Thanks for everyone's time and responses!

 

[LostInCodeLand]

Hi JLGWhiz! Your code worked perfectly and your clear directions were so easy to follow! I can't thank you enough for sharing your expertise - now I can complete my inventory system! Thank you again and have a great day!

 

[JLGWhiz]

Hi JLGWhiz! Your code worked perfectly and your clear directions were so easy to follow! I can't thank you enough for sharing your expertise - now I can complete my inventory system! Thank you again and have a great day!

You are welcome,
Regards, JLG