Sorry for the delay in getting back to you its mainy due to time difference.
I highly appreciate your help and time Sir. Thank you again for this kind help and advise.
The purpose of enabling calculation is because the workbook has many iteration and there are also other sheets which are calculated that is why i have set the calculation to manual and only calculates when we run the codes.
I am also converting the formula to values so as not to increase the workbook file size.
I was really looking to do it with VBA but i have not been able to figure it till now but am still thinking of how to do it with VBA as you advise.
If you can guide me this will be a lot of help please.
I am attaching a sample for better understanding of the calculation for the part we are discussing. Normally we have around 600-700 rows of data to analyse when we plan the orders. i have put like 95 rows of data in the sample file for better understanding and also to decrease the file size.
Also there are many hidden columns on the sheet which is very important i have just hide it and leave only the column which are concern for the calculation.
I have assign the codes for the loading on the button on top name " Click to load" and i have assign also the code for painting on the button " click to paint" for better understanding when you will test it. The code for the painting is in fact the code you have help me with to decrease the time taken for painting.
You will see there are lot of dependencies before the calculation can process. Like the 1st day Limit,2nd day limit,3rd day limit from column BE to BG. Also for the start date of the next order it depends on the previous order completion date for a particular line and also which date we have defined on the column BC and column AY for the capacity adjustment.
Please try to run it at your end you will get it how it works actually.
Thank you again for the time and i am open for any suggestions and advice for doing it in VBA instead of formula.
Reply With Quote