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.)
Scanning barcodes and matching them to existing numbers
Discussion
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?
i hope that makes sense.
You didn't answer so I assume you have no knowledge of VBA.
No need to copy the code - which goes "behind" the sheet - or type anything.
In terms of using it with other inventory sheets, the macro works with the layout you gave, no matter how many rows you have
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!)
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.
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).
Answers
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!