help with vba calculation on several columns based on row.

[VISHAL120]

am new to vba and i need some advise on how i can make a calculation based on a limit cap on 1 column and place the remaining values to another that is the values that is placed on the column shall not be greater than that of the cap and it continue with other values.
i done it in a loop but it has not work till not. many thanks for the help in advance.



example column A has 500 as qty. and the daily produce limit in column C is 200 , col D is 250 column E is 250 therefore when doing the calculation:
it shall place the first 200 in col C ,

the 2nd 250 on col D
and the remaining on Col E that is 50 as qty.
and this loop for another quantity in col A and start directly in col E as the produce limit has already been full for col C,D,
it has to control that the qty in each column shall not go beyond the produce limit and it shall continue to place the value till the produce limit is equal to the qty in a col.


please see the attachements for better idea.


many thanks in advance for the help.

[Bob Phillips]

Put this formula in C6

=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)

and copy down and across

[VISHAL120]

am new to vba and i need some advise on how i can make a calculation based on a limit cap on 1 column and place the remaining values to another that is the values that is placed on the column shall not be greater than that of the cap and it continue with other values.
i done it in a loop but it has not work till not. many thanks for the help in advance.



example column A has 500 as qty. and the daily produce limit in column C is 200 , col D is 250 column E is 250 therefore when doing the calculation:
it shall place the first 200 in col C ,

the 2nd 250 on col D
and the remaining on Col E that is 50 as qty.
and this loop for another quantity in col A and start directly in col E as the produce limit has already been full for col C,D,
it has to control that the qty in each column shall not go beyond the produce limit and it shall continue to place the value till the produce limit is equal to the qty in a col.


please see the attachements for better idea.


many thanks in advance for the help.


hi xld,

many thanks for the formula it works,but my problem is that if i will use formula like this the file become bigger and takes time to calculate and simulate that is why i want to do it in vba code. thanks to see any possibility please.

[Bob Phillips]

How many instances of the formula will you have that will give you that problem?

[VISHAL120]

How many instances of the formula will you have that will give you that problem?

HI XLD,

many thanks again for your prompt reply. in fact the file is already 6mb. and the part am discussing is part of the program. i am sending the real file how it shall be working and that also is a part of the program. please find below a brief explantion of how it works. you can view it on the file also i have place a brief notes on the file also for better understanding.
THE FILES SHALL WORK LIKE THIS :

1. The order will be group in the order group col that means when the second order will be starting there is no production loss as all the setting is same and their will be a continuity on the production and therefore when placing on the calculation zone it shows the continuity..

2. The order shall be place in the calculation zone according the daily cap defined in the daily cap col and verify the produce limit according to the module prod being place in the module prod col. It shall continue loading the qty till the stil to load is Zero.i have already created a function for this. Check it if ts ok.

3. The qty shall be place same as explain in the previous files send that is if an order is 3000 and the limit is 1200, it shall put only the first 1200 in the col where the limit is not use and the balance in another col.
4. When placing the qty on the calculation zone it has also to verify that the order end date is respected.That is qty cannot cannot go beyond the order end date.

5. if ever the order end date cannot be respected it shall give a debug print msg to the user advising the order which cannot be respected

6. It has to pick up also the start date from the calculation zone headerand show it on the start date col.


thanks again for the help in advanced to do this in vba as in formula it will be wise working with as the time it will take will be much more.

[VISHAL120]

HI XLD,

many thanks again for your prompt reply. in fact the file is already 6mb. and the part am discussing is part of the program. i am sending the real file how it shall be working and that also is a part of the program. please find below a brief explantion of how it works. you can view it on the file also i have place a brief notes on the file also for better understanding.
THE FILES SHALL WORK LIKE THIS :

1. The order will be group in the order group col that means when the second order will be starting there is no production loss as all the setting is same and their will be a continuity on the production and therefore when placing on the calculation zone it shows the continuity..

2. The order shall be place in the calculation zone according the daily cap defined in the daily cap col and verify the produce limit according to the module prod being place in the module prod col. It shall continue loading the qty till the stil to load is Zero.i have already created a function for this. Check it if ts ok.

3. The qty shall be place same as explain in the previous files send that is if an order is 3000 and the limit is 1200, it shall put only the first 1200 in the col where the limit is not use and the balance in another col.
4. When placing the qty on the calculation zone it has also to verify that the order end date is respected.That is qty cannot cannot go beyond the order end date.

5. if ever the order end date cannot be respected it shall give a debug print msg to the user advising the order which cannot be respected

6. It has to pick up also the start date from the calculation zone headerand show it on the start date col.


thanks again for the help in advanced to do this in vba as in formula it will be wise working with as the time it will take will be much more.