VBA Stock List creation

[mikeopp]

Hello All,

I am new here and as usual someone with a coding problem.
New to excel vba but have had many years of access vba coding.

I have a sheet with a range of products (6 chr Uppercase text) and quantities, each represents
one pallet of stock in a row in warehouse.
I need to create a stock take list of the row name (first row of sheet) then each individual (unique)
product and count in that row. Some rows can have multiple different products.
Have been looking at the Scripting Dictionary as it returns the fastest results, so far unsuccessfully.

Any assistance would be greatly appreciated.

Mike

[nankw83]

Hi Mike & welcome to the forum,

Try below code based on you sample file ... I don't know what's supposed to be under "Product" & "Count" since it doesn't show anything in your sample file

Please Login or Register  to view this content.

[mikeopp]

WOW, that's an impressive piece of coding.
I will need a bit of time to go through it (to learn)

Exactly what I was looking to do, thank you Nankw83

[sintek]

Just for the fun of it...Another option...

Please Login or Register  to view this content.

[nankw83]

thank you Nankw83

Glad to help & thanks for the added Rep+ ... If the provided solutions take care of your original question, please take a moment & mark the thread as [SOLVED] from the Thread Tools above your first post

[mikeopp]

Hi Sintek,
Smooth, another fine solution. straight into the stock take sheet. Nice to read too.

Thank you

[sintek]

.........................
Thanks.png

[mikeopp]

Very close to a complete solution, need to bounce a couple more things off you.
Have an updated example attached for you.
Slightly changed the code and added a full clearer example.
Questions as follows

Question 1. Sheets("Stocktake").Range("A3").Resize(x, 5)
how do you preserve the content of the 3rd column (product)

Question 2. I have broken the code into 4 groups for each area to loop through,
can this be put into one piece of code

Question 3. You will note in the count column i have varying numbers, these are part pallets
is there an easy way to bring these in too

Also with both lots of code I get occasional miscounts or it will put in 2 consecutive rows with the same code. (I can work with that though)
Thank you again, your help is greatly appreciated
Mike

[sintek]

I know you were not referring to me in above, but had some time...
Your initial Stock Sheet set up & layout should be more thought out...
I moved your Product Table to the stock sheet and added one more entry in [B80] to allow that to become one area...

Please Login or Register  to view this content.

[mikeopp]

Now that's nice work. That's a tenth of the master sheet, it grew as it was needed. Of course I haven't found
time to rearrange it... yet :D
Thank you Sintek for your time once again, and an ideal solution.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[mikeopp]

Thankyou AliGW. I have already given Sintek and Nankw83 rep.

[AliGW]

However, you have not marked the thread as solved. Please do so. Thanks.

EDIT: You have now!

[sintek]

All good Mike...thanks...happy coding...