1. 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```

2. I just edited the OP's post #36 to Un-Tabluate the text from the tables.

3. @p45cal

This might improve speed too:

```If colm > 1 Then
sn= Evaluate("transpose(row(1:" & colm - 1 & "))")

cells(j,5) = Application.Sum(.Index(resultsAry, rw, sn))
End If```
But you should realise that Evaluat is a very compactly written arrayformula. Arrayformulae do not speed up things in general.

4. 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.

5. All in acordance with what I wrote in #39.

Good that you tested it yourself.

6. 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:
```With ThisWorkbook.Sheets("Shadow_Normal_Calc")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
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
ReDim resultsAry(1 To .Range("AY60:EX" & LR).Rows.Count, 1 To .Range("AY60:EX" & LR).Columns.Count)

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)
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
AllPreviousDaysMins = 0
For i = 1 To colm - 1
AllPreviousDaysMins = AllPreviousDaysMins + resultsAry(rw, i)
Next i

If IsError(i) Then Capacity = Empty Else Capacity = CapacityAry(i, colm)

For i = 1 To rw - 1
If LineDeptAry(i, 1) = LineDept Then
End If
Next i
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```
It takes a considerably shorter time to produce the results.

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

7. 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.

8. Originally Posted by VISHAL120
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.
That is just the type of thing I was referring to when I said:
Originally Posted by p45cal
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).
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.

9. This sounds like a Worksheet Design problem.

Can you save the workbook as an Excel 2003 (xls) file and post it?

10. @SamT

Can you try to update your system to at least Office 2010 ?

11. ## advice needed to speed this code

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.

12. Originally Posted by VISHAL120
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.
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).

13. Can you try to update your system to at least Office 2010 ?
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.

14. 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.

15. 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.

16. Originally Posted by VISHAL120
the starting date for the next dept which is dependent from the previous dept or if their is a date set manually.
Give me an example, somewhere in the middle of the grid, of a dept, then show me all the cells involved for all 3 sections in blue in the quote above.

17. So i want to know what you want to express by saying:
I am having a conversation with snb.

18. ## advice needed to speed this code

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

19. 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.

20. SamT ,

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•