Solved

Adding & Increasing Inventory Quantity Automatically When Entering Stock Number

Posted on 2013-02-02
11
440 Views
Last Modified: 2013-02-03
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
Comment
Question by:etakumi
  • 6
  • 5
11 Comments
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
The "attached" file is missing. Please try again?
0
 

Author Comment

by:etakumi
Comment Utility
Sorry - here is the file
Invoice-Master.xlsm
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
How do you insert the scanned item ID into the Invoice worksheet? I was expecting code.
0
 

Author Comment

by:etakumi
Comment Utility
We scan in a barcode that is equivalent to the stock number.
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
How does the scanned number enter the worksheet?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:etakumi
Comment Utility
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

by:Faustulus
Comment Utility
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

Open in new window

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

by:etakumi
Comment Utility
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

by:Faustulus
Comment Utility
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

by:
Faustulus earned 500 total points
Comment Utility
This should make you happy, :-)
Please test.
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

Open in new window

0
 

Author Comment

by:etakumi
Comment Utility
OMG!  You rock!  You made my day - thanks so much!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now