Macro to list variant items of a barcode

[jayherring86]

Hello all,

I am looking for a way to streamline my excel workbook. I use certain items which fall under the same barcode and wish to be able to assign variant ID's to them.

For instance, I may have several items that differ by colour under the same actual barcode so I use another column called "Variant" and in that i write "red", "blue" etc.

I then recall the information to alter the state of that particular stock. I do this by scanning the barcode into col B, then using an array formula to get the variant names in Col D. Using a data validation dropdown I can then choose the variant that I want to work with. This works well but the problem is that my worksheet has to be able to look at 50+ items at a time, and having 50+ array formulas on a sheet is going to be extremely memory-hungry.

If a better answer exists, I am happy to look into it but I am wanting to turn the following array formula into a macro that can be activated when I have finished entering all of the barcodes and wish to list the variants for my validation list...

Please Login or Register  to view this content.

The code might be familiar to more advanced users, for each of the rows in my barcode worksheet, there are 10 of these formulas that will list up to 10 seperate variant ID's per barcode (so for a list of 50 barcodes, there are 500 array formulas using up processing power!).

I think that the best solution to my problem is to simply make a macro that:

• Looks at the barcode, if there is one present in B8
• Finds and lists all of the Variant ID's (@Transactions!$D:$D), where b8 matches Transactions!$B$3:$B$23000
• Moves to the next barcode present in col B
• until it finds a blank cell.

I'd like the list for each barcode to be placed after col HV but as long as i can select the range for use in a data validation list i am not strict with how the data is presented.

Many thanks in advance, I hope I have described the original problem, and my suggested solution clearly. I can elaborate if needed.

- Dom

[walruseggman]

For clarity and consistency it's better to name all your worksheets in the code, but I didn't see a worksheet name for your barcodes. You'll need to plug in that sheet name in the first line of code where I have "Barcodes", otherwise this code won't work.

Please Login or Register  to view this content.

[jayherring86]

Hello and thank you for your reply.

The macro is running fine with only 1 or two snags. First is minor, in that the name you gave the sub made for some interesting behavior (in that selecting "macros" would select the cell JH86 and not allow for me to actually run the macro lol. An easy fix

The second is that the resulting list is printed in just one cell, with carriage returns between each entry. Can you make your code output each list item to a separate line (HV8:HV18)? - There will never be more than 10 different variant names so 10 rows is more than adequate.

I appreciate your help (and hope you find employment opportunities for your VBA skills soon!)

[walruseggman]

I think this is what you want? Again, change the barcode sheet name.

Please Login or Register  to view this content.

[jayherring86]

That code works okay, thank you. However if there is more than one barcode listed on col B, it loops through and lists all possible variants on column HV.

Is there a way to loop through ten times (to give me the 10 rows of variant ID's, and then move to the next barcode and print the results 1 column over (HW ++)?

I appreciate your time, and simply by contrasting the two pieces of code you have given so far I have learnt a lot