I have been using a planning system which till now was working very well and also I was able to build the planning system with the help from vb express itself. Thanks a lot for the this help.
But the way it was working till now was very helpful with the formula and then I was using a vba code to copy paste the formula into values every time so that not to increase the size of the file.as this is a part of the planning system
The formula is as such IF($R19="",0,IF(F$33<$O19,0,IF(F$33>=$O19,IF(0<($AA19),MIN(($AA19-SUM(E19:$AF19)),$AB19,SUMIF(Shadow_km_Module,$R19,F$3:F$32)-SUMIF($R18:$R$34,$R19,F$34)),0))))
This formula is the heart for the planning system without this the planning is nothing as every time all analysis is done first with this and others can be done.
I have attached the file so that you can have a better idea of how it works..you can click on the button to see how it works to have an idea. Or just copy the formula from the parameter data and paste it on the shadow_k_mins where the calendar starts the calculation area is not formatted but when there is figures it will format through conditional formatting..
When the system is running there is lot of anaylsis which shall be generated at one go.
As you know every time that an order is not respecting the dates it shall be changed and the analysis shall be run again to have a better results and this has been done till now with 500-600 rows analysis at one go.
But now it will be working with almost 500 to 6000 rows almost 3 -4 times than the actual. And this will take a lot of time for the anaylsis. I have made a simulation with 2000 rows it has freeze and after almost 3 -4 mins that the anaylsis has been done and it very time consuming. All other parts has been program using vba. Its only this part that uses formula..
What I need is how I can program this through vba instead of using the formula. And this will be very beneficial for the planning system and also for the user.
Reply With Quote