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

Follow up question about scanning barcodes


I recently had a question on here 

The title was "Scanning barcodes and matching them to existing numbers"

Link to original post 


It was answered beautifully using this code

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 = ""
' Allow events to work again
Application.EnableEvents = True

 End Sub

Example file sheet with the code is attached.

the issue im having is that when i scan a tag # that isnt found in Columbn E a popup message appears that i have to manually clear. What i would like is for any tag # not found to be thrown in a "not found" list, Column I, for example could be a list of anything scanned and not found. this way i can just go through scanning items without being interupted and when the scan is done the missed items would be represented by an unpopulated line in Column F next to its corresponding tag #  and the extra items would be listed in Column I.



Selected Answer


In the revised file attached, I've modified only the end of the full code (extract below, changes in bold) to do different things if an item is found more than once (Fnd > 1, you get a modified warning) or not at all.

If it's not found (Fnd=0), then it puts the tag in column I and the "timed" message in columns J. (Note: this is a revision to my original answer)

Given you don't want to be stopped by messages, I've added some code so that the colour of H1 toggles between yellow and green so that you know something has happened (it either went in E/F or I/J). Try it with an unknown number and see that it adds items (from row 3) for you to complete/transfer (though this could be assisted by another macro).

'<< existing code>>
Next n
' If it wasn't found...
If Fnd = 0 Then
    ' calculate last row in column H
    LstRw2 = Range("I" & Rows.Count).End(xlUp).Row
    ' OPTION- if it's empty, use last from E instead
    'If LstRw2 < LstRw Then LstRw2 = LstRw
    ' set values in I and J of row below to suit
    Cells(LstRw2 + 1, 9) = Target.Value
    Cells(LstRw2 + 1, 10).Value = Target.Value & " located " & Now
End If

' If it was found more than once...
If Fnd > 1 Then
    '... alert user
    MsgBox "Tag #" & Target.Value & " found " & Fnd & " times in column H" & Chr(13) & "Please check"
End If
' clear H1 value and reselect it (for next entry)
Target.Value = ""
' toggle the cell colour so user knows something happened
If Target.Interior.Color = vbYellow Then
    Target.Interior.Color = vbGreen
    Target.Interior.Color = vbYellow
End If
' Allow events to work again
Application.EnableEvents = True

End Sub

Hope this makes sense and works with your scanner/ way of working.



i see what you did there but is there no way to just create a seperate "not found" column on the same sheet? the reason for this is because i have multiple different warehouses that have their own inventory lists. One of those warehouses has, literelly, thousands of tags. All i really need is a "not found", could be anywhere. the reason why i said Column I was because my idea would be if its found it goes to the right with the date and time if its not found it goes to the left, i dont even need the date and time for the not found tags. i just need a list of the problem tags so i can copy/paste them into my system and find out whats going on with them.
JL125 (rep: 4) Aug 2, '22 at 4:47 pm
sorry, meant to found goes left and not found goes right.
JL125 (rep: 4) Aug 2, '22 at 4:48 pm
A not found column is easier in fact but do you just want those to start adding from row 2 or after all known rows? I added the "timed" message" to not found items so the data sheet could have the same details when you resolve the issues.
John_Ru (rep: 3647) Aug 2, '22 at 5:22 pm
I assumed you wanted Not founds at the top (if you have 100s of entries)- see my revised answer and file. That could be changed easily so they're after the end of the known list but harder to spot (I already put an option for that in the modified code).

I shifted the header row down and forgot to say the loop now needs to start )
For n= 3 to..

Note that my solution addresses your question/ sample file (as it should). It might be too slow if you have thousands of rows (but there are ways around that). Suggest you try.
John_Ru (rep: 3647) Aug 2, '22 at 5:50 pm
this is perfect this, is exactly what i need. Before this i was printing out a 40 page inventory list and going around and reading all the tags and cecking them off by hand. with this, all i have to do is set my laptop up and then go scan crazy and then come back to see what i have to research.
Mr. John_Ru, you have made my life 1000 times easier. 

the only thing i would like to know is, if i need to, how can i adjust the columns that are being searched. for example, if i want to move the "serial #" column over to column D then the "Item#", "Tag#" and "Last Found" column would all shift to the right 1 space. this would mean that the bacode scan cell would have to search Column F instead of column E and stick the found items in Column G instead Column F. Is that as simple as going into the code and changing an E to an F and an F to a G, or is it more complicated than that?

The only reason for this would be if i wanted to make my inventory list look more aesthically pleasing so if its like a huge complicated thing i can just leave it as is. it's already a million times easier with what you've made for me here. 
JL125 (rep: 4) Aug 3, '22 at 10:33 am
Glad that worked for you and thanks for selecting my Answer, Justin

If you're planning to change the macro, you need to take care (given you had no knowledge of VBA two days ago and I can't teach you VBA in a message- that's why I recommended you take a course or study some of Don's tutorials!).

If you "move the "serial #" column over to column D then the "Item#", "Tag#" and "Last Found" column would all shift to the right 1 space" then several things would need to change since the tags will be in column F.

E.g. you would need lines like (changes in bold):
'find last row in column F
LstRw = Range("F" & Rows.Count).End(xlUp).Row

' if value matches...
    If Target.Value = Cells(n, 6).Value Then
        ' add a statement in column G
        Cells(n, 7).Value = Target.Value & " located " & Now

since you now want to check in column 6 (F) and add text in column 7 (G).

Likewise in the section starting:
' If it wasn't found...
If Fnd = 0 Then
' calculate last row in column J
    LstRw2 = Range("J" & Rows.Count).End(xlUp).Row

etc. for lines with Cells too.
John_Ru (rep: 3647) Aug 3, '22 at 10:57 am
p.s. I'm guessing you're on the West coast of the USA. I accept Californian grape products! :)
John_Ru (rep: 3647) Aug 3, '22 at 11:01 am
Also please check this code works reliably with bigger lists- I did it in a rush and offer no guarantees that it will work flawlessly (though it should).
John_Ru (rep: 3647) Aug 3, '22 at 11:03 am
i was right about to do a full inventory of my warehouse to test it out. 

thanks for the quick answers. my boss has some coding knowledge and a basic understanid of VBA so he might be able to take a look at it for minor adjustments.

i think i will look into some VBA courses. this is really useful stuff.

JL125 (rep: 4) Aug 3, '22 at 12:02 pm
If you're quick, Don's VBA course still has 60% off (but the offer only 2 hours left)
John_Ru (rep: 3647) Aug 3, '22 at 12:21 pm
Or, check out the VBA subsection of Tutorials, as I said before. That doesn't give a structured approach however. Good luck anyway. Don't forget those grapes!
John_Ru (rep: 3647) Aug 3, '22 at 12:31 pm
unfortunately im at work right now so i couldnt react to that deal that fast but its fine.

i did an invetory with about 300 or so line items and it worked like a charm.

thank you very much.
JL125 (rep: 4) Aug 3, '22 at 4:38 pm
also, im sorry, i dont understand what you mean about californian grape products. 
JL125 (rep: 4) Aug 3, '22 at 4:41 pm
Glad it worked well, Justin. Ask another question if it proves too slow with bigger inventories. 

I was only joking about "grape products" and referring to wine (if indeed you're in California or a wine-producing state). I'll settle for my usual reward- your thanks and kind words 
John_Ru (rep: 3647) Aug 3, '22 at 5:23 pm
lol, i get it now. unfortunately, i live in Florida and we're not exactly known for wine down here. i can send you a bottle of orange juice, though. you can make a killer mimosa. 

thanks again for all your help. 
JL125 (rep: 4) Aug 4, '22 at 8:29 am
:- ) I'll be fine! Florida seems a nice state though; from my experience.
John_Ru (rep: 3647) Aug 4, '22 at 8:39 am
hey, would you mind shooting me your email address. i'd like to inquiry with you about something and i dont want possibly violate any rules on this site.
JL125 (rep: 4) Aug 4, '22 at 2:26 pm
Justin. Sorry but we don't give out our personal email addresses here, especially now since the site has been hit recently by loads of spamner questions.

Primarily though, as a volunteer, I wouldn't want to be seen as a free personal consultant to a bunch of people. You might argue that I am already but I choose to do so (and could stop anytime, without being chased on email by solution-hungry users).

If you need to ask a question, just check the Rules (link in blue text   Alerts | My Profile | Help | Rules  above) and ask away! So long as it relates to Excel, (and not black magic or the likes, like the spammers), it should be fine.
John_Ru (rep: 3647) Aug 4, '22 at 4:00 pm
Add to Discussion

Answer the Question

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