Creating an automated inventory system using excell and a barcode scanner

Devorr

New Member
Joined
Aug 30, 2021
Messages
1
Office Version
  1. 2011
Platform
  1. Windows
I need to do inventory using a barcode scanner and excel. How do I make it so after I scan it the program will search the old inventory spreadsheet for the matching barcode and copy the barcode as well as the adjacent cells (with the item names and serial numbers) into the new spreadsheet.

I would be eternally grateful for help with this as this would cut down a tremendous amount of work from me. Thank you in addvance:)

This is my Frankenstein of a code I make from stuff that does what I want it to do but I can't glue it together.

Sub inout()
Dim barcode As String
Dim rng As Range
Dim rownumber As Long
Dim Cl As Range
Dim Dic As Object


barcode = Worksheets("Sheet1").Cells(2, 2)

Set rng = Sheet1.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
ActiveSheet.Columns("a:a").Find("").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Select

Else
rownumber = rng.Row
Worksheets("Sheet1").Cells(rownumber, 1).Select
ActiveCell.Offset(1, 0).Select
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2")
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Cl.Offset(, 1).Value
Next Cl

End With
With Sheets("Sheet1")
For Each Cl In .Range("M2", .Range("M" & Rows.Count).End(xlUp))
If Dic.exists(Cl.Value) Then Cl.Offset(, 1).Value = Dic(Cl.Value)
Next Cl
End With
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top