First of all - great site. I have been reading through the forum for quite some time and have found many threads quite valuable. But it is time to create a thread for my own since I have not been able to find a discussion for my question.
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another worksheet.
I have one worksheet with the following field and data:
ItemId....Red....Yellow....Green....Multiple....Inventory position
0001.... 10.... 30.... 50.... 5.... 45
0002.... 5.... 40.... 47.... 5.... 23
0003.... 11.... 20.... 30.... 10.... 5
I would like to generate new rows (in another worksheet) based on the above fields and three rows.
Basically the end result should look as the following:
ItemId....Qty....Start inv....Aggregated inventory....Prioritization
0002.... 5.... 23.... 28.... Yellow
0002.... 5.... 28.... 33.... Yellow
0002.... 5.... 33.... 38.... Yellow
0002.... 5.... 38.... 43.... Green
0002.... 5.... 43.... 48.... Green
0003.... 10.... 5.... 15.... Red
0003.... 10.... 15.... 25.... Yellow
0003.... 10.... 25.... 35.... Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in worksheet 2) is above the green value.
The priotization value should be based on the start inv (in ws 2) compared to the values in red, yellow and green in ws 1.
It would be much valuable if someone could guide me in the right direction towards an appropriate VBA design. I have found some code online which could solve the frame structure but I am looking for what to place into the middle of the code section.
I am looking forward some great answers.
Much appreciated - thank you in advance.
Bookmarks