Solved

# Adding & Increasing Inventory Quantity Automatically When Entering Stock Number

Posted on 2013-02-02
449 Views
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.
0
Question by:etakumi
• 6
• 5

LVL 14

Expert Comment

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

Author Comment

ID: 38848081
Sorry - here is the file
Invoice-Master.xlsm
0

LVL 14

Expert Comment

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

Author Comment

ID: 38848086
We scan in a barcode that is equivalent to the stock number.
0

LVL 14

Expert Comment

ID: 38848090
How does the scanned number enter the worksheet?
0

Author Comment

ID: 38848102
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

LVL 14

Expert Comment

ID: 38848130
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 Comment

ID: 38848142
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

LVL 14

Expert Comment

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

LVL 14

Accepted Solution

Faustulus earned 500 total points
ID: 38848186
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

Author Comment

ID: 38848832
OMG!  You rock!  You made my day - thanks so much!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦