Scan barcodes in each category in Excel. Select the category and then scan in. All templates, with code, are available for purchase for $50 USD
www.easyexcela...
Click this link to check out my one-on-one training www.calendly.co...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
This template is available for purchase for $50 USD
www.easyexcela...
How to insert VBA code in Excel • How to insert VBA code...
Sub add()
Dim lrow, erow As Long
Dim serow As Long
Dim cnum, rnum As Long
Dim prodrng, rng1 As Range
Dim rng3, sold As Range
Dim barcode, cate As String
Dim scol, r As Long
Dim answer As Integer
Dim cnt, scnt As Long
'set scan in cell as prodrng
Set prodrng = ActiveCell
If prodrng = "scan here" Then Exit Sub
cnum = prodrng.Column
cate = Sheet1.Cells(1, cnum).Value
barcode = Sheet1.Cells(3, cnum).Value
'enter the serial number in the last row of the column
lrow = ActiveSheet.Cells(Rows.Count, cnum).End(xlUp).row + 1
erow = ActiveSheet.Cells(Rows.Count, cnum).End(xlUp).row
'seach to see if value already there
Set rng1 = Sheet1.Range(Cells(4, cnum), Cells(10000, cnum)).Find(what:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng1 Is Nothing Then
addin:
Sheet1.Cells(lrow, cnum).Select
ActiveCell.Value = barcode
ActiveCell.Interior.ColorIndex = 43 'green
GoTo inventory
Else
rownumber = rng1.row
'count how many times it is in list
cnt = 0
r1 = 4
For r1 = 4 To erow
Sheet1.Cells(r1, cnum).Select
If ActiveCell.Value = barcode Then
cnt = cnt + 1
End If
Next r1
'check to see if it has been sold
Sheet2.Activate
'find the column that this is in
Set sold = Sheet2.Range("a1:bs1").Find(what:=cate, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
scol = sold.Column
'determine the last row in this column
serow = ActiveSheet.Cells(Rows.Count, scol).End(xlUp).row
r = 6
'Sheet2.Cells(r, scol).Select
scnt = 0
For r = 6 To serow
Sheet2.Cells(r, scol).Select
If Sheet2.Cells(r, scol).Value = barcode Then
scnt = scnt + 1
End If
Next r
If scnt = cnt Then
GoTo question
Else
MsgBox "this product has not ben rented,so cannot be entered again"
GoTo ende
End If
question:
Sheet1.Activate
answer = MsgBox("This product has been entered twice, Do you want to add it again?", vbQuestion + vbYesNo + vbDefaultButton2, "In stock")
If answer = vbYes Then
Sheet1.Cells(lrow, cnum).Select
ActiveCell.Value = barcode
ActiveCell.Interior.ColorIndex = 6 'orange
Else
GoTo ende
End If
End If
'*************************************************************
inventory:
Sheet3.Activate
Set rng3 = Sheet3.Range("A:A").Find(what:=cate, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
rnum = rng3.row
Sheet3.Cells(rnum, 3).Value = Sheet3.Cells(rnum, 3).Value + 1
'***************************************************
ende:
Sheet1.Activate
ActiveSheet.Cells(3, cnum).Select
With ActiveCell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
ActiveCell.Value = "scan here"
ActiveCell.Interior.ColorIndex = 44 ' orange
End Sub
Негізгі бет Scan barcodes in each category in Excel
Пікірлер: 5