数量宏excel，用于库存 [英] quantity macro excel for inventory

问题描述

我正在尝试让Excel用作库存扫描读取器.我在扫描时遇到了麻烦，因为它在扫描时会不断添加到col A中，即使它是重复的，我也希望它能够识别出扫描的项目与上一个相同并添加到col数量中.如果有人可以帮助我解决代码问题，那就太好了！

I am trying to get Excel to work as a inventory scan reader. I'm having trouble with scanning because it constantly adds to col A when I scan and even if its a duplicate, I want it to be able to recognize that the item scanned is the same as the last and add to the col QTY. If any one could assist me as how to go about the code that would be great!

图片-我不会提出，但是COL A是条形码，COL B是Description，COL C是数量.

Pictures--It wont let me put one up but COL A is Barcode, COL B is DESCRIPTION and COL C is QTY.

推荐答案

将此代码放置在工作表代码模块中(右键单击选项卡，然后选择查看代码").

Place this code in the sheet code module (right-click on the tab and select "view code").

您的列表标题进入A4(条形码)B4(描述)C4(计数). 选择单元格A1并开始扫描.如果在列表中找到匹配项，它将更新计数，否则，它将添加新行.我只是通过输入值进行测试，但是只要扫描仪每次扫描都发送{enter}，就应该使用扫描仪进行同样的操作.

Your list headers go in A4 (barcode) B4 (description) C4 (count). Select cell A1 and start scanning. If it finds a match in the list it will update the count, if not then it adds a new line. I tested just by typing in values, but should wortk the same way with a scanner as long as it's sending {enter} with each scan.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SCAN_CELL As String = "A1"
    Const RANGE_BC As String = "A5:A500"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 2)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        f.Offset(0, 1).Value = "enter description"
        f.Offset(0, 2).Value = 1
    End If

    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True

    Target.Select

End Sub

这篇关于数量宏excel，用于库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！