PDA

View Full Version : calculation col and rows min and max values



VISHAL120
08-28-2010, 03:52 AM
Hi all,

I have seen many of the thread posted and is very glad to see the number of thread solve. I have search for some of the thread which I can use to solve my but till I have not been able to use them for my problem and am really block with my work due to this and actually am doing this manually which take a lot of time.
I have also make us of this formula MIN(MIN(MAX(0,$A6-SUM($C$2:C$2)+C$2+SUM($C$5:C5)),MAX(0,SUM($C$2:C$2)-SUM($C$5:C5)),C$2),$A6), same posted to me previously .
This works when I was having only one condition. But right now there is more than one condition:
The data is like that col C is order qty , col D is daily cap by 1 module. And a calculation is done to know in how many days the order will complete. Therefore it takes for example order : 1500, daily cap module 1 = 400, days to complete is 1500/400 = 4 days.
And the same is placed on the daily plan as follows col G = 400 col H = 400 , Col I = 400 col J = 300 and the total will be 1500.
Here even thought the cap for module 1 daily is on col G is 500 it continue to plan as per the limit cap per day for the item.

And this continue as such for the other item.
It shall also plan the item as explained above and shall not go beyond the total cap for each module. That is if the total cap for module 1 is 500 it shall split the qty for each item and shall not plan more than 500 on a daily basis. See example for module 2 to better understand.

Please can anyone just give me a started code on VBA for the above. As am really block with this doing it manually and by day I have a lot of item like that.
See attached details for better understanding.