Need Help Badly with Scanning in Barcodes

[mitekcomputers]

I truly need some help as I am NOT prtoficient with Excel at all and never have been.

I am trying to create a very simple inventory sheet for a client. I will be using symbol barcode scanners and will not be anywhere near the computer as parts are scanned.

I need ONLY to scan the barcode that is the serial number for the part, and then scan the quantity that is received.

I am using a Symbol scanner Model DS 3478.. no buttons, wireless USB with dock... it simply has a trigger and nothing else BUT will work wirelessly up to about 300 feet or so. I have it set to do the carriage return/line feed..so after a scan, it drops down one cell to scan something else.

Heres what I need and HOPEFULLY someone here is much smarter than I.

This customer has no more than 25 part numbers (serial numbers/barcodes)

These are in a barcode format and when scanned drop out the part number such as S1190111.

I need to scan this barcode, and then drop to the next cell and scan the quantity, and then go BACK to the first cell to scan another part number and then back down to scan the quantity for that part. This would basically be the scan in sheet for receiving all parts as they arrive.

On a separate tab (sheet) within the same worksheet, I would have all the part numbers listed, and one cell over from each part number, have a TOTAL quantity in stock field.

As I scan part numbers and quantities in on sheet 1, I need it to update the quantities on sheet two (tab 2) for each respective part number.

Heres my problem... first and foremost, when I scan the quantity barcode it always has a Q in front of the true quantity.. ie 500 pieces scans out as Q500..1000 pieces scans out as Q1000.

I need something to make it drop or remove the Q when I scan in the quantity and just place the number 500 (or whatever the quantity is) into this field and then add it to sheet 2 TOTAL quantity for that particular part number.

Secondly, when I scan the part number in the first field, it auto drops to the cell below and that is basically where I want it to place the quantity I scan from the same card on receipt.

After its scanned I would like it to jump back UP to the part number field that is now empty/blank or whatever, scan in the 2nd card, drop down one cell, and scan in the qty for the second card, and back up again and so on and so forth.

In all reality on the 1st scan in sheet it could just keep going down the list as opposed to jumping back up as long as it would keep updating the quantities on sheet 2.... either way would be fine.

In other words, part number, qty, part number, qty part number, qty while UPDATING the quantities for the corresponsing part numbers on sheet 2..

Is this possible and if so.. how?

Office products have always been my weak spot.. I am a hardware guru from hell, but stuff like this just drives me crazy. I used to have a friend that always took care of these type things for me, but unfortunately he passed away earlier this year, and now I have no one that can help.. and I am totally lost.

I do not know if what I want is even possible in Excel. .I may have to use Access, but whatever I need is what I need

Thanks in advance for any help

Mike

 

[vaskov17]

The code below assumes the following:
1. There are sheets named "Scan" and "Parts"
2. The part number goes into cell "A1" in the "Scan" sheet
3. The quantity goes into cell "A2" in the "Scan" sheet

Place the code in the "Scan" worksheet object in the VBA editor


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim i As Integer
Dim objRange As Range
Dim objFound As Range
Dim strPart As String
Dim intQuantity As Integer

On Error GoTo ErrHandle

If Worksheets("Scan").Range("A1").Value <> "" And Worksheets("Scan").Range("A2").Value <> "" Then
strPart = Trim(Worksheets("Scan").Range("A1").Value)
intQuantity = Int(Right(Trim(Target.Value), Len(Trim(Target.Value)) - 1))

Set objRange = Worksheets("Parts").Range("A:A")

Set objFound = objRange.Find(strPart, , xlValues, xlWhole, xlByRows, xlNext)

If objFound Is Nothing Then
i = 1
Do
If Len(Trim(Worksheets("Parts").Range("A" & i).Value)) = 0 And Len(Trim(Worksheets("Parts").Range("B" & i).Value)) = 0 Then
Worksheets("Parts").Range("A" & i).Value = strPart
Worksheets("Parts").Range("B" & i).Value = intQuantity
Exit Do
Else
i = i + 1
End If
Loop
Else
Worksheets("Parts").Range("B" & objFound.Row).Value = Int(Worksheets("Parts").Range("B" & objFound.Row).Value) + intQuantity
End If

Worksheets("Scan").Range("A1").Value = ""
Worksheets("Scan").Range("A2").Value = ""

Worksheets("Scan").Range("A1").Select
End If

Application.EnableEvents = True

Exit Sub

ErrHandle:
Application.EnableEvents = True
End Sub

 

[mitekcomputers]

Thank you so much for your code... it is EXACTLY what I needed and it works flawlessly.

I have looked through the code and its still beyond me how any of this works, but work it does and I am most grateful.

Mike