In that case; if you want to sum left of cell in column5:
Sub M_snb() sn = Cells(1).CurrentRegion For j = 2 To UBound(sn) sn(j, 5) = Evaluate(Split(Join(Application.Index(sn, j), "+"), "+" & sn(j, 5))(0)) Next End Sub
In that case; if you want to sum left of cell in column5:
Sub M_snb() sn = Cells(1).CurrentRegion For j = 2 To UBound(sn) sn(j, 5) = Evaluate(Split(Join(Application.Index(sn, j), "+"), "+" & sn(j, 5))(0)) Next End Sub
I just edited the OP's post #36 to Un-Tabluate the text from the tables.
The post is now readable.
I always expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
@p45cal
This might improve speed too:
But you should realise that Evaluat is a very compactly written arrayformula. Arrayformulae do not speed up things in general.If colm > 1 Then sn= Evaluate("transpose(row(1:" & colm - 1 & "))") cells(j,5) = Application.Sum(.Index(resultsAry, rw, sn)) End If
snb, after testing of several of the above one-liners, I've reverted to my original looping code as it's still more then 10 times as fast.
All in acordance with what I wrote in #39.
Good that you tested it yourself.
VISHAL120, I've spent as much time on this as I'm going to spend.
I think the end of your final formula should be:
…-SUMIF($AM$58:$AM59,$AM60,AY$58:AY59))))))
It's taking me too long to work out how calling Block_date_Start_Date interferes, as when this is included (the comment apostrophe removed) we need several iterations (more than 6) for the results to become stable (but still there remain some 15 cells with different results).
In the attached is some code which I'm 99.5% sure is the equivalent of this long formula; this part:It takes a considerably shorter time to produce the results.With ThisWorkbook.Sheets("Shadow_Normal_Calc") LR = .Cells(.Rows.Count, "A").End(xlUp).Row FR = .Range("Shadow_Normal_Calc_Header_Row").Row + 1 WIPAry = .Range("AF" & FR & ":AF" & LR).Value 'col AF MaxMinDateAry = .Range("AJ" & FR & ":AJ" & LR).Value 'col AJ LineDeptAry = .Range("AM" & FR & ":AM" & LR).Value 'col AM MinsLoadedAry = .Range("AN" & FR & ":AN" & LR).Value 'col AN MinMinsAry = .Range("AO" & FR & ":AO" & LR).Value 'col AO CapacityAry = .Range("AY5:EX56").Value DateRowAry = .Range("shadow_Normal_Calc_Calendar_Row").Value ReDim resultsAry(1 To .Range("AY60:EX" & LR).Rows.Count, 1 To .Range("AY60:EX" & LR).Columns.Count) ShadowDeptLinesSumCol = .Range("$AV$5:$AV$56").Value For rw = 1 To UBound(resultsAry) If rw Mod 10 = 0 Then Application.StatusBar = rw 'keeps the user informed of progress. WIP = WIPAry(rw, 1) MinsLoaded = MinsLoadedAry(rw, 1) MinMins = MinMinsAry(rw, 1) MaxMindate = MaxMinDateAry(rw, 1) LineDept = LineDeptAry(rw, 1) For colm = 1 To UBound(resultsAry, 2) ResultsColumnDate = DateRowAry(1, colm) If WIP = "" Or MaxMindate = "" Or ResultsColumnDate < MaxMindate Then resultsAry(rw, colm) = 0 Else If MinsLoaded > 0 Then AllPreviousDaysMins = 0 For i = 1 To colm - 1 AllPreviousDaysMins = AllPreviousDaysMins + resultsAry(rw, i) Next i RemainingMins = MinsLoaded - AllPreviousDaysMins i = Application.Match(LineDept, ShadowDeptLinesSumCol, 0) If IsError(i) Then Capacity = Empty Else Capacity = CapacityAry(i, colm) SameDayAndLineDeptCapacityAlreadyUsed = 0 For i = 1 To rw - 1 If LineDeptAry(i, 1) = LineDept Then SameDayAndLineDeptCapacityAlreadyUsed = SameDayAndLineDeptCapacityAlreadyUsed + resultsAry(i, colm) End If Next i RemainingCapacity = Capacity - SameDayAndLineDeptCapacityAlreadyUsed resultsAry(rw, colm) = Application.Min(RemainingMins, MinMins, RemainingCapacity) Else 'there is no 'else' in the original formula. End If End If Next colm Next rw .Range("AY60").Resize(rw - 1, colm - 1).Value = resultsAry
I've altered your Function Start_Date_Plan to make it less convoluted and faster.
In the attached are a couple of sheets you should delete: Start which is a copy of the values returned by your formula (after correction). Sheet3 just contains formulae to compare newer results with Start, with the count of different cells in cell DB1 and any dissimilar cells highlighted in red.
Whether you use formulae or code to get these values I doubt very much you can rely upon them until you sort out these circular references. As a 'for-instance', using your attached file in msg#1, if you delete part of the results area, say cells AY60:BK75, you'll need to click your button 3 or 4 times, each with its attendant wait, before the results are stable and the cells in AW60:AW75 are all zero. I'm guessing it's likely to need many more button-clicks if the whole grid was cleared.
If calculations are mostly being done in-memory, there's little advantage to turning screen-updating and auto-calculation off, then on again.
Investigation shows that most of the time taken by your code isn't actually placing the formula in the cells - that takes place in a fraction of a second - most of the time is taken calculating the cells' formulae. Excel will calculate the results of a formula placed in a cell by vba immediately, whether or not calculation is set to manual.
I'd be interested to know how fast the code above is on your computer.
The file was too big to attach (extra check sheets) so it's here: https://app.box.com/s/j34teqs8eptgk8v48f13pkdbiyymk0wc
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hi P45Cal,
thanks so much for your time and patience.
I have run the code and if not removed the actual data calculation the time is 4.05 secs.
But when i have clear all the calculated data i have had to click almost 5 times to have the required results and with a total time of 15.53 secs. Can you please advise as normally with each change down on quantities or minimum qty on an order we will need to clear the previous data before previewing the newly calculated data.
is there a possibility that it do the calculation on one click as to understand if all calculation has been done we will need to check all the orders is are well loaded.
thanks for your suggestion and thank you again for the great help.
That is just the type of thing I was referring to when I said: The problem is that some of the cells needed to calculate the main part of the grid are themselves dependant on what's in the grid. You need to make sure that all cells which are used in calculating the main grid are NOT dependant on any of the cells in that main grid.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
This sounds like a Worksheet Design problem.
Can you save the workbook as an Excel 2003 (xls) file and post it?
I always expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
@SamT
Can you try to update your system to at least Office 2010 ?
Hi everybody,
I am unable to attach the file right now i will attach it tonight for version in 2003.
I am actually using excel 2010 and 2013.
concerning the dependency of the calculation within the grid, its difficult to remove as it shall take into consideration what qty has been planned previously and calculating the remaining qty to planned. That is why it will depend on the previous value calculated on the grid. I have try to figure out it this can be done in another method but still has not been able to.
example if we have 2,500 mins in total to plan with a min minutes per day of 450 it will be calculated as follow :
day1 day2 day3 day4 day5 day6
450 450 450 450 450 250.
Thus it will always check the cumulative planned previously to plan the qty remaining.
thanks again for helping and to recommend new possibilities if possible.
It is all right for the formula to check on other cells within the grid (it does this for previously calculated cells to the left and above a given cell in the grid).
It is not OK for the cells in the main grid to use cells outside the grid, which in turn refer to cells within the grid in their formulae.
You need to check the formulae in the cells outside the grid which your original formula referred to and:
1) See if they can get their data from outside the grid
2) Or see if they're needed at all in the original formula, or whether the equivalent info can be gained from elsewhere (outside the grid).
Last edited by p45cal; 05-25-2016 at 04:23 AM.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I am saving to buy a hard drive so I can update my system to a different computer. A month after that, I can buy a used Office 2007 dvd.Can you try to update your system to at least Office 2010 ?
I always expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
SamT,
I did not understand your message can you be more explicit on what you want to tell.
In my Office am using 2010 and home am on 2013 is there any problem for that.
Please i respect all people on the forum and i value the people,their time, their patience and recommendations. Especially those who are helping us to grow in our knowledge and work. Frankly speaking this forum has help me solve many of our repetitive work that were done by people manually for which i always give credit to the person helping and to the forum in concern.
So i want to know what you want to express by saying:
SAMT
I am saving to buy a hard drive so I can update my system to a different computer. A month after that, I can buy a used Office 2007 dvd.
Hi P45Cal,
Thank you again for your precious time.
The parameters that it need outside the calculation grid are :
1. the starting date for the next dept which is dependent from the previous dept or if their is a date set manually.
2. the total mins to plan and the minimum mins capacity for the day.
These are the info that it required from outside and which are prerequisite for the calculation to run. The way You have proceeded its very helpful and quicker. we need only to find out the several click to complete the whole calculation as before that their are other calculation that need to be done before coming two this one. I have been trying to wrap it all but it shows still problem as we need to check the through the 600 rows if all has been plan or not.
p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I am having a conversation with snb.So i want to know what you want to express by saying:
I always expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Hi P45Cal,
Please find attached an example as requested. It starts from Row 77 to 82 which shows an order with its complete dept from Cut to Finish.
I have also place some text messages for each of the column to show which column and row are dependant to each other and all the column which are dependent i have coloured it yellow as pattern and blue for the fonts which will easily identify and to read.
I have also leave to formula for these rows so that we can see the calculation on the grid and the column used if we try to edit the formula. This will be easier to understand also i guess.
thanks .
vishal
I think that you are trying to eat the entire Elephant at one meal.
How do you eat an elephant? One small meal at a time.
There are things that Excel can do much better than people and there are things that people can do much better than Excel.
I think that you are trying to make Excel work like your people have worked for so long that you are much better at it than Excel can be at doing it the way your people do it.
More later.
I always expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
SamT ,
thanks for your comments for the elephant.
for your information this was running well when we planning on 100 rows before with formula. But now its not possible as we need to plan uo to 1,500 rows due to the factory capacity which has increase and that is why i am opting to do it with VBA.
Also in textile we always has dept to start based on previous dept date and for me this what we call planning in our sector and if this is the case do you think we shall put the date manually for it to calculate. Its impossible then in that case you are asking me to make the planning sit 3 hours with 10 people together to be able to give a good planning to production people.
I understand it complex and difficult that is why i need help on it.
Do you have a better recommendation of doing it.