[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

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.
0
etakumi
Asked:
etakumi
  • 6
  • 5
1 Solution
 
FaustulusCommented:
The "attached" file is missing. Please try again?
0
 
etakumiAuthor Commented:
Sorry - here is the file
Invoice-Master.xlsm
0
 
FaustulusCommented:
How do you insert the scanned item ID into the Invoice worksheet? I was expecting code.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
etakumiAuthor Commented:
We scan in a barcode that is equivalent to the stock number.
0
 
FaustulusCommented:
How does the scanned number enter the worksheet?
0
 
etakumiAuthor 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
 
FaustulusCommented:
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
 
etakumiAuthor 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
 
FaustulusCommented:
OK, this is the other side of the same coin I pointed out. I'll put a fix to it.
0
 
FaustulusCommented:
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
 
etakumiAuthor Commented:
OMG!  You rock!  You made my day - thanks so much!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now