Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Scanning barcodes and matching them to existing numbers

0
In my example inventory sheet, Column E contains the barcode tag #. If I physically walk up to an item and scan the barcode the tag # will populate in whatever cell I have selected and then move down to the next cell, so if i want i can just scan every tag i see and end up with a whole column of tags. what i would like to do, if it's possible, is scan a tag into a cell in column H and then have excel look for that number in column E and, if it finds the number, take it from column H and put it in column F next to its matching number from column E.
Answer
Discuss

Discussion

Hi and welcome to the Forum 

Sorry but I don't understand your question (even if I assume "shoot" / "shot" relate to inserting values into cells).

Please EDIT your question above to clarify what you mean (i.e. don't answer this item) . E.g. cell E2 contains tag# 210407000091 (and F3 is empty). What happens when you scan a matching item and what value should go into column F (which has no heading)?

Regarding a possible solution, do you have any knowledge of VBA?
John_Ru (rep: 3632) Aug 1, '22 at 4:53 pm
In my example inventory sheet, Column E contains the barcode tag #. If I physically walk up to an item and scan the barcode the tag # will populate in whatever cell I have selected and then move down to the next cell, so if i want i can just scan every tag i see and end up with a whole column of tags. what i would like to do, if it's possible, is scan a tag into a cell in column H and then have excel look for that number in column E and, if it finds the number, take it from column H and put it in column F next to its matching number from column E.

i hope that makes sense.
JL125 (rep: 4) Aug 1, '22 at 6:23 pm
Thanks but I asked you to edit your question to add that detail (so anyone answering the question sees the full story in one place) - I will have very little time tomorrow so probably won't be able to reply but others might.

You didn't answer so I assume you have no knowledge of VBA.
John_Ru (rep: 3632) Aug 1, '22 at 6:42 pm
sorry about that, i edited the post. also, no, i dont have any knowledge of VBA
JL125 (rep: 4) Aug 1, '22 at 9:45 pm
ok so i did a bit of research on VBA. turns out i can turn on macros on my excel without any issues. i copied and pasted your code into the macro and i assume im supposed to hit run but when i do all i get is a window that pops up that just says "Macros" at the top and a space to type in a Macro Name. again, im not very familiar with VBA at all so i dont know what im missing here.
JL125 (rep: 4) Aug 2, '22 at 2:31 pm
I just noticed your example sheet that you attached here and this is exactly what im looking for. i need to be able to take this macro you created and apply it to other inventory sheets that i'll make in the future.
JL125 (rep: 4) Aug 2, '22 at 2:36 pm
JL. If you have enabled macros in Excel, just open the file I provided and put a  number in H1 of the worksheet. The "event" macro will be triggered automatically and a match made (or an error message presented) .

No need to copy the code - which goes "behind" the sheet - or type anything. 
John_Ru (rep: 3632) Aug 2, '22 at 2:38 pm
If that worked for you, you should mark my Answer (below) as Selected- this guides others and increases our reputations in the Forum. This and your thanks are all I get for my efforts to help you! 

In terms of using it with  other inventory sheets, the macro works with the layout you gave, no matter how many rows you have
John_Ru (rep: 3632) Aug 2, '22 at 2:45 pm
i showed my boss and he thinks this is fantastic, the only thing he asked about is what happens if i can a tag and it doesnt find it. so we tried and it pops up an error message. is there any way to have the not found items go into a not found column?
JL125 (rep: 4) Aug 2, '22 at 2:46 pm
JL. Firstly please Select my Answer ( as requested).

Secondly, did the macro work with your scanner? 

Finally I'd suggest a scanned  "not found" item be added to E but the macro could only add the tag (and timed message) in the next free row - you need to ask a separate question about that (we can't allow one question to roll on with many "what about" requests!) 
John_Ru (rep: 3632) Aug 2, '22 at 2:56 pm
p.s. Most of the last comments should have been in the Discussion below the Answer (not the Question). It's your first time here so no problem but please look at the Rules for future
John_Ru (rep: 3632) Aug 2, '22 at 2:59 pm
sorry about that

I have selected your answer here, it's brilliant.

The scanner does work with the macro that you have created.

I dont have a minor issue still but i can start another question if thats more in line with the rules here.
JL125 (rep: 4) Aug 2, '22 at 3:06 pm
Yes please but refer to this question and  include the file and code in the text. Use the CODE button and paste the "Select All" code text over the string "Code_Goes_Here".

I ask this since I'm just a volunteer here and don't always have time (and sometines the knowledge) to answer all questions. If not, a couple of other contributors might (so would need the detail in one place).
John_Ru (rep: 3632) Aug 2, '22 at 3:13 pm
Add to Discussion

Answers

0
Selected Answer

JL

The best way of doing this is using VBA (Excel's built-in programming language). To use the .xlsm file I've created, you will need to "enable macros" if they are not enabled already (they are disabled by default and sometimes by corporate IT policies). If you try the file attached as directed below and nothing happens, search the Internet to see how to enable them e.g. the Microsoft guidance here Macros in Office files. It varies with Excel version (and your Forum Profile doesn't say which version you use) but isn't available on web-based or phone versions of Excel. 

I've used a special "event" macro which is triggered when a value in Sheet1 is changed (or entered). With macros enabled, MANUALLY put a known value in yellow cell HI, say 211124000753. The macro should automatically enter timed message* like this in column F against that tag in E...

211124000753 located 02/08/2022 09:02:52

(date time will match your locale) and return to a cleared cell H1. Enter a number in H1 that is not in column E (say 12), and you'll get a message.

The work is done by this code (where the bits in red below are comments and only there so you have an idea of what is happening):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Fnd As Long, LstRw As Long, n As Long

' check only value of H1 changed
If Target.CountLarge <> 1 Or Intersect(Target, Range("H1")) Is Nothing Then Exit Sub
' prevent this being triggered again by itself
Application.EnableEvents = False
'find last row in column E
LstRw = Range("E" & Rows.Count).End(xlUp).Row
' loop from 2 to that row
For n = 2 To LstRw
    ' if value matches...
    If Target.Value = Cells(n, 5).Value Then
        ' add a statement in column F
        Cells(n, 6).Value = Target.Value & " located " & Now
        ' increase Found counter (from 0)
        Fnd = Fnd + 1
    End If
Next n
' If it wasn't found once...
If Fnd <> 1 Then
    '... alert user
    MsgBox "Tag #" & Target.Value & " found " & Fnd & " times in column H" & Chr(13) & "Please check / add new details in row " & LstRw
End If
' clear H1 value and reselect it (for next entry)
Target.Value = ""
Target.Select
' Allow events to work again
Application.EnableEvents = True


End Sub

* The timed message will be some proof of finding and could be used with conditional formatting to identify items not found more recently than ago say (but that would be a separate question!)

Hope this fixes things for you (if so, please marked the Answer as Selected). I don't have a barcode scanner to check that it works with this code (with H1 selected and a scan done) but I think it will- please confirm.

Hopefully you will see some of the power of VBA - you might consider taking Don's excellent paid course "Excel VBA Beginner to Expert" to improve your working life!

Discuss

Discussion

Thanks for selecting my Answer, JL. Hope your boss is happy and perhaps sees the benefit of you taking a structured course in VBA (as I suggested) . At the least he should let you spend some time in the VBA section under the Tutorials section of this site- Don has put up many great videos to help you. Good luck selling that!
John_Ru (rep: 3632) Aug 2, '22 at 3:05 pm
thank you very much.
JL125 (rep: 4) Aug 2, '22 at 3:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login