Using Barcode scanner to search through sheet with existing SKUs

[saltymalty]

I have seen a few posts on this, but can't find a specific solution for what I'm looking for. I work in a retail environment where we conduct daily counts, and do so on an excel sheet.

We currently call out the first 4 digits of a given barcode, and enter the quantity into one of two columns. We have a sales floor, and a vault, and need the quantity in both to be accurate in accordance with our records. The main thing I am hoping to accomplish, is to use a barcode scanner to find a SKU, and allow us to populate the cell in column G, then Column H.

I have experimented with ctrl+F and it is a bit too clunky to work efficiently for our flow. Thanks in advance for any help on this!

[Keebellah]

Hi, welcome to the forum.
If you have a barcode scanner there's also software to read the data and for example use it in Excel.
You will have to implement and use macros, these will do the Ctrl+F for you and then of course you need some code to write.

[sintek]

Do your products already have the barcode stickers on?
I assume Package Id is the Sku No...
Floor & Vault same as Front & Back Stock?...

We currently call out the first 4 digits of a given barcode

When you say call out...do you mean capture or enter...
Are the first 4 digits unique with every product...

[Keebellah]

Sintek, a barcode scanner does need the barcode font, some scanners have their software that reads the barcode and you can use it to find a value in for example Excel that's just tet like any other and all you need is to implement it a macro.
But all barcode scanners have their own software s it's not a 1-2-3 answer for the OP

[sintek]

Sintek, a barcode scanner does need the barcode font

Hans you are so right...Just tested that...
I assume OP justs wants to enter the first 4 digits of the sku code and code to make use of wildcard search and update...

[Keebellah]

well, but that again is possible if you can get the string into a variable you can search for any occurrence of that string or part in a field

[sintek]

saltymalty...Give this a go...
On opening Scanning box pops up...
Enter stock counts in two boxes and then enter first 4 sku digits in scanning box and press enter...

Please Login or Register  to view this content.

[Keebellah]

What if the 4-digits are already present?

[sintek]

What if the 4-digits are already present?

Assumes first 4 digits are unique...
Also, as this is a daily count...OP has not mentioned whether values are overwritten or incremented...
Can only wait until a response I suppose...
Guess one will also have to incorporate error trapping if less than 4 or more than 4 digits is entered...

Please Login or Register  to view this content.

[Keebellah]

@sintek: just an observation. I didn't check for it, but the updating worked great.
I added a barcode with the same 4 last digits as test and it doesn't do anything

[sintek]

I added a barcode with the same 4 last digits as test and it doesn't do anything

Should only capture if wildcard was placed before

Please Login or Register  to view this content.

[Keebellah]

first one worked without wildcard, that's why I asked, but still the OP can work it out

[sintek]

Oh, Now I get it...just tested...
Red snippet must be xlWhole

Please Login or Register  to view this content.

New file attached....

[Keebellah]

I was just curious, but honestly, did not think of looking at the code itself

[saltymalty]

Do your products already have the barcode stickers on?
I assume Package Id is the Sku No...
Floor & Vault same as Front & Back Stock?...

When you say call out...do you mean capture or enter...
Are the first 4 digits unique with every product...

Thanks for the response,

Yes front and back stock is what we're working with. The items do have barcode labels, we just currently say the first four digits of a sku and find in our sheet. Looking to eliminate that process by scanning and receiving a prompt for quantity entry.

Sorry for the delay here, and thanks for the response!

To clear up the confusion on the first four. That is our current process for ease of access, but we are looking to scan and lookup the ENTIRE SKU in the sheet. Could you clarify what you mean by overwritten vs incremented?

If you look at the example sheet, we need to verify that current stock matches the number in column F. So there would need to be a way to scan an item, entire the amount in the given location (front or back stock), and move on.

[saltymalty]

Sorry for the delay here, and thanks for the response!

To clear up the confusion on the first four. That is our current process for ease of access, but we are looking to scan and lookup the ENTIRE SKU in the sheet. Could you clarify what you mean by overwritten vs incremented?

If you look at the example sheet, we need to verify that current stock matches the number in column F. So there would need to be a way to scan an item, entire the amount in the given location (front or back stock), and move on.

[saltymalty]

Oh, Now I get it...just tested...
Red snippet must be xlWhole

Please Login or Register  to view this content.

New file attached....

This is awesome!! Thanks so much for putting this together. I am a total VBA noob, how could i go about editing the textbox so that it can accept the full SKU?

[sintek]

Apologies...was away for a couple weeks...
Remove this part...

Please Login or Register  to view this content.

Change this part...

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.