# Adding & Increasing Inventory Quantity Automatically When Entering Stock Number

I have an Excel workbook created in Excel 2010.  There are 2 worksheets - Invoice and Inventory.

The Invoice worksheet has 7 columns::

A=Stock Number
B=Item Description
C=Quantity
D=Price
E=Discount %
F=Discount Amount
G=Total

The Inventory worksheet has 3 columns:

A=Stock Number
B= Item Description
C=Price

On the Invoice worksheet, the  Stock Number is entered by scanning a barcode into the first cell (A17).  Then, formulas pull the Description and Price from the Inventory worksheet.

I would like to be able to have the initial quantity (1) entered automatically into the Invoice worksheet in the appropriate cell (first cell would be C17).  Then, whenever the same barcode is scanned, instead of adding another line item, it would increase the count of the first scanned identical barcode.

Any help would be greatly appreciated.  I've attached the file for reference.
###### Who is Participating?

Commented:
This should make you happy, :-)
``````Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range
Dim IsStockNo As Range
Dim R As Long
Dim IsRepeat As Boolean

Set Rng = Range("A17:A40")
Set IsStockNo = Application.Intersect(Target, Rng)
If Not IsStockNo Is Nothing Then
Application.EnableEvents = False
With Target
R = FindItem(.Value, Rng)
If R <> .Row Then
.ClearContents
IsRepeat = True
End If
.Offset(1 + IsRepeat).Select
End With

With Cells(R, "E")
.Value = .Value + 1
End With
Application.EnableEvents = True
End If
End Sub

Function FindItem(ByVal SearchFor As Variant, _
SearchIn As Range) _
As Long
' return the number of the row in SearchIn
' where SearchFor was found (or 0)

Dim Fnd As Range

With SearchIn
Set Fnd = .Find(What:=SearchFor, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
If Not Fnd Is Nothing Then FindItem = Fnd.Row
End Function
``````
0

Commented:
The "attached" file is missing. Please try again?
0

Author Commented:
Sorry - here is the file
Invoice-Master.xlsm
0

Commented:
How do you insert the scanned item ID into the Invoice worksheet? I was expecting code.
0

Author Commented:
We scan in a barcode that is equivalent to the stock number.
0

Commented:
How does the scanned number enter the worksheet?
0

Author Commented:
We just put the cursor in the first Stock Number cell (A17), scan the barcode with the barcode scanner and the stock number is entered.  You could also just enter in the stock number - works the same way.  Scanning the barcodes just eliminates the need to manually enter the stock number.

All the merchandise for sale has a tag with the relevant barcode on one side of the price tag and the price on the other.  All we have to do once a customer selects their merchandise is to scan the barcode from the tag and the stock number is entered into the worksheet and then the formulas in the other cells looks for the identical barcode on the inventory sheet and then pulls in the appropriate description and price.

We could enter the quantity manually once separating the merchandise, but it would so much easier to just keep scanning barcodes and the quantities are entered/updated.  Unfortunately, I have no clue about VBA so I'm unable to figure out how to do it.
0

Commented:
Please paste this code in the code sheet of the INVOICE worksheet.
``````Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range
Dim IsStockNo As Range

Set Rng = Range("A17:A40")
Set IsStockNo = Application.Intersect(Target, Rng)
If Not IsStockNo Is Nothing Then
With Target.Offset(0, 4)
.Value = .Value + 1
End With
End If
End Sub
``````
Note that the code doesn't guard against errors. For example, if you scan item #1 to A17 the quantity in row 17 will be 1. If you next scan item #2 into A17 the item will be #2 in column A and the quantity will be 2.
0

Author Commented:
I tried it, but it doesn't quite do what I would like.  It's great as it adds a quantity of 1 when you scan in the item into Row 17/Cell A17.  But, when I scan in the identical barcode say, 3 rows down (Row 20), I was hoping that it would increase the quantity in Row 17 to 2 and leave Row 20 blank for entry of another item.

If course, this is way better than what I could do.
0

Commented:
OK, this is the other side of the same coin I pointed out. I'll put a fix to it.
0

Author Commented:
OMG!  You rock!  You made my day - thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.