Solved

Adding & Increasing Inventory Quantity Automatically When Entering Stock Number

Posted on 2013-02-02
11
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 14

Expert Comment

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

Author Comment

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

Expert Comment

by:Faustulus
ID: 38848083
How do you insert the scanned item ID into the Invoice worksheet? I was expecting code.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

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

Author Comment

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

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

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
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

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

by:
Faustulus earned 500 total points
ID: 38848186
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
ID: 38848832
OMG!  You rock!  You made my day - thanks so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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