1. Originally Posted by SamT Not bad for a first time poster Welcome aboard.
Thanks for the comment, looking again , I realise that my solution is incomplete for what was required, however the basic principle of minimising the number of accesses to the spreadsheet is something I have used many times to speed up macros.  Reply With Quote

2. Hi ,

i have tried the above code but when running its placing the formula only on column AY and not on the Range("AY59:EX" & lLR) = inarr.

Can you please check and advise if this is the case as i have run it several times and it continue to place it on the column AY only.

thanks again for helping.

And i really need to figure out on this problem as we are suppose to present the planning to the department by next week but just because of the time its taking we are squeeze right now.

I would be very grateful for the help and recommendation on that,.  Reply With Quote

3. Hi,
apologies for my incomplete answer, but try this which uses the same technique but does a double loop

```Sub test()
With ThisWorkbook.Sheets("Sheet1")
lLR = Cells(Rows.Count, "A").End(xlUp).Row
End With

inarr = Range("AY59:EX" & lLR)
Application.StatusBar = " Automated Planning : Computing ...."

'    With Range("AY59:EX" & lLR)
'        '.Application.Calculation = xlCalculationAutomatic
'        .Value = .Value
'    End With

endarray = 59 - lLR
' column ex is column 154 column ay is column 59
endcol = 154 - 51

For i = 1 To endarray
For j = 1 To endcol
Next j
Next i

Range("AY59:EX" & lLR) = inarr
End Sub```  Reply With Quote

4. Hi,

Thanks for helping.

In fact i have just tested the code and i think its placing the formula only on the column AY and not on the :
Range("AY59:EX" & lLR) = inarr

thanks again for helping.  Reply With Quote

5. Hi Vishal 120
I actually tested it this time, I changed the formula so that it made sense on my computer, but the principal is the same. There were a few coding errors ( looping round a negative index doesn't work) this code took 4 secs on my computer see how yours does:
(I put numbers from 1 to 445 in the rows A1 to A445)
Note the R1C1 reference in my equation is used to demonstrate changing the formula for each row (or column), I don't know whether your equation needs this but it is a useful technique to know about.

```Sub test()
startTime = Timer
With ThisWorkbook.Sheets("Sheet1")
lLR = Cells(Rows.Count, "A").End(xlUp).Row
End With
inarr = Range("AY59:EX" & lLR)
Application.StatusBar = " Automated Planning : Computing ...."
'    With Range("AY59:EX" & lLR)
'        '.Application.Calculation = xlCalculationAutomatic
'        .Value = .Value
'    End With
endarray = lLR - 59
' column ex is column 154 column ay is column 59
endcol = 154 - 51
For i = 1 To endarray
For j = 1 To endcol
inarr(i, j) = "=sum(r1c1:r" & i & "c1)"

Next j
Next i
Range("AY59:EX" & lLR).Formula = inarr
MsgBox Timer - startTime & " secs."
End Sub```  Reply With Quote

6. Originally Posted by offthelip the With range statement means the code accesses the spreadsheet multiple times
This is not true. Originally Posted by offthelip the basic principle of minimising the number of accesses to the spreadsheet is something I have used many times to speed up macros.
That is true, however the original code gains access only once to the sheet with:
`.Formula = "=IF(OR(\$AF59="""",\$AF59="""",\$AJ59=""""),0,IF(AY\$58<\$AJ59,0,IF(AY\$58>=\$AJ59,IF(0<(\$AN59),MIN((\$AN59-SUM(AX59:\$AX59)),\$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,\$AM59,AY\$4:AY\$56)-SUMIF(\$AM\$58:\$AM58,\$AM59,AY\$58))))))"`

This line:
`Range("AY59:EX" & lLR).Formula = inarr`
is no more efficient than this line:
`.Formula = "=IF(OR(\$AF59="""",\$AF59="""",\$AJ59=""""),0,IF(AY\$58<\$AJ59,0,IF(AY\$58>=\$AJ59,IF(0<(\$AN59),MIN((\$AN59-SUM(AX59:\$AX59)),\$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,\$AM59,AY\$4:AY\$56)-SUMIF(\$AM\$58:\$AM58,\$AM59,AY\$58))))))"`

VISHAL120, I suspect strongly there is a circular reference on the sheet, only it takes some looking to find out where it is as Excel doesn't seem to report it (probably to do with the UDFs). I will look again when I get time (your formulae are many and long (and not always correct!)) and I'm not 100% sure what you intend with the formulae.  Reply With Quote

7. I have just compared this with your solution and I think mine is actually slower, so I don't think this is going to help  Reply With Quote

8. Never use Excelformulae in VBA
Never use UDF's in VBA

Do all the calculation in arrays

```Sub Block_date_Start_Date()
sn = Sheet2.Cells(58, 1).CurrentRegion.Resize(, 156)

For j = 3 To UBound(sn)
If sn(j, 32) >= 0 Then
For jj = 51 To UBound(sn, 2)
If sn(j, jj) > 0 Then Exit For
Next
sn(j, 43) = sn(1, jj)
End If
Next

....

End Sub```  Reply With Quote

9. Originally Posted by snb Do all the calculation in arrays
This what I have so far done and get the same results as VISHAL120's formula but find that I have to run the code more than once to get the same result, hence my suspicion of circular refs. (cells in AY60:EX660 depend on cells outside that area, but some of those precedent cells in turn depend on values within AY60:EX660 - I've just got to find a chunk of time to find it/them).

Note also that cells in AY60:EX660 depend on cells within AY60:EX660 too (those in the same column above and in the same row to the left) and the order of my in-memory calculations takes account of that - perhaps Excel's on-sheet formulae calculate in a different order, but I doubt it.

A CLEAR explanation of what the relevant columns represent and what the output in AY60:EX660 is meant to represent from VISHAL120 would be helpful.  Reply With Quote

10. Originally Posted by p45cal This what I have so far done.
I seem to be unable to find your code ???  Reply With Quote

11. Originally Posted by snb I seem to be unable to find your code ???
I haven't offered it yet!  Reply With Quote

12. for snb, my stage of development (you'll see frequent refs to 66, only because I was translating the code from a random single cell (BE66)!)
So far just to ensure I get the same results as VISHAL120's corrected formula (msg#15), then I'll try and make it faster/shorter/more elegant.
You'll see a reference to you, snb, in one of the comments!
```Sub Main_Normal_Calculation2()
Dim lLR As Long

With Application
'.Cursor = xlWait
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
lLR = .Cells(.Rows.Count, "A").End(xlUp).Row
AFary = .Range("AF" & FR & ":AF" & lLR).Value
AJary = .Range("AJ" & FR & ":AJ" & lLR).Value
AMary = .Range("AM" & FR & ":AM" & lLR).Value
ANary = .Range("AN" & FR & ":AN" & lLR).Value
AOary = .Range("AO" & FR & ":AO" & lLR).Value
BigAreaAboveAry = .Range("AY5:EX56").Value
ReDim resultsAry(1 To .Range("AY60:EX" & lLR).Rows.Count, 1 To .Range("AY60:EX" & lLR).Columns.Count)

For rw = 1 To UBound(resultsAry)
af66 = AFary(rw, 1)
an66 = ANary(rw, 1)
ao66 = AOary(rw, 1)
aj66 = AJary(rw, 1)
am66 = AMary(rw, 1)
For colm = 1 To UBound(resultsAry, 2)
Debug.Assert Not (rw = 2 And colm = 3)
'=IF(OR(\$AF66="",\$AJ66=""),
If af66 = "" Or aj66 = "" Then
resultsAry(rw, colm) = 0
Else
'we have the equivalent of =IF(BE\$58<\$AJ66,0,IF(BE\$58>=\$AJ66,"srumpf")) where we're askibng 2x about the value of BE58 v AJ66 in 2 if statements, only need if then else:
be58 = DateRowAry(1, colm)
If be58 < aj66 Then  'put this in first IF?
resultsAry(rw, colm) = 0  'put this in first IF?
Else  'BE\$58>=\$AJ66 automatically unless strings involved.
'if(0<(\$AN66)...
If an66 > 0 Then
'the first argument in the MIN function:
'(\$AN66-SUM(\$AX66:BD66)) this will need values to the left to be calculated first!
mysum = 0
For i = 1 To colm - 1  'do this by slicing and dicing arrays a la snb instead? Test for speed.
mysum = mysum + resultsAry(rw, i)
Next i
MIN1 = an66 - mysum

'the 2nd argument in the MIN function \$AO66:
'ao66

'the 3rd argument in the MIN function :
If IsError(i) Then MIN3a = Empty Else MIN3a = BigAreaAboveAry(i, colm)

'-SUMIF(\$AM\$58:\$AM65,\$AM66,BE\$58)
'awaiting explanation/correction from OP. Nonsense at the moment.It's always = 0.
MIN3b = 0
For i = 1 To rw - 1
If AMary(i, 1) = am66 Then
'          Stop
MIN3b = MIN3b + resultsAry(i, colm)
End If
Next i

MIN3 = MIN3a - MIN3b  'awaiting clarification

resultsAry(rw, colm) = Application.Min(MIN1, ao66, MIN3)
Else
'there is no else in the worksheet formula.
End If
End If
End If
Next colm
Next rw
'Sheets("Sheet3").Cells(1, 1).Resize(rw, colm).Value = resultsAry
.Range("AY60").Resize(rw, colm).Value = resultsAry

With .Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
'.Application.Calculation = xlCalculationAutomatic
'''.Value = .Value
End With
End With
Block_date_Start_Date  'p45cal moved

With Application
' .Cursor = xlDefault
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub```  Reply With Quote

13. These are the two formulas from the OP's #1, but there have been several corrections to them

Does anyone have the current error-less versions?

```    With Range("AY59:EX" & lLR)
.Value = .Value
End With

With Range("AW59:AW" & lLR)
.Formula = "=AN59-SUM(AY59:EX59)"
.Value = .Value
End With```  Reply With Quote

14. Originally Posted by Paul_Hossler These are the two formulas from the OP's #1, but there have been several corrections to them

Does anyone have the current error-less versions?
For the first I think it's this and I think these two are the equivalent:

For the second, apart from it not starting at row 60 and thus being
```With Range("AW60:AW" & lLR)
.Formula = "=AN60-SUM(AY60:EX60)"
.Value = .Value
End With```
I haven't yet twigged what's wrong with it.  Reply With Quote

15. @p45cal

Ahhh, now I see !
Thank you.

I wish the OP could describe his/her goal.
The unraveling of clumsy formulae takes too much time.  Reply With Quote

16. ## Advice needed to speed this code.

Hi,

Again thanks for the precious time and helpful hand.

The last corrected formula is this one:

The goal of this is to plan the whole factory starting from the cutting dept to finishing of the product.
1.Under the Factory there are different dept which are defined on range AV5 to AV52.
2.Normally the sequence of preceding for every order are different but all of them shall undergo to cutting, makeup .
3.Each Dept has to start 2 days after the start of the previous dept same is being checked by the routine Block_Date_Start_Date.
4.The Check for each dept and lines is down on column AM.
5.Only the Cutting Dept starts according to the max date achieved from Fabric date, Trims date,PPS,Pre Run and same is being checked by the routine Block_Date_Start_Date.
6.Each dept has a capacity allocated by day and we shall be planning according to the dept capacity until the capacity is fully loaded for that date and move to another day.

 CUT 1400 1400 1400 1400 1400 1400 1400 1400 1400 1400 1400 HEMB 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 MEMB 1100 1100 1100 1100 1100 1100 1100 1100 1100 1100 1100 SPRAY 800 800 800 800 800 800 800 800 800 800 800 WASH 800 800 800 800 800 800 800 800 800 800 800 2_JOBURG 500 500 500 500 500 500 500 500 500 500 500 3_ROME 500 500 500 500 500 500 500 500 500 500 500

7.Also each order can have a Minimum Mins to be plan for the day as this depends upon the complexity of the specific order which is entered and decided during the planning. That is why we have a Min Mins Column which is column AO

 Order Ordered qty Wip Qty Fabric date @ factory Trims @ factory PPS Pre Run Dept SYSTEM BLOCK DATE BLOCKED DATE MANUAL MAX MIN DATES mins Loaded Min Mins Str Date A 3000 2000 4-Apr 4-Apr 10-Mar 1-Apr CUT 4-Apr 4-Apr 2000 1500 9-May A 3000 1800 4-Apr 4-Apr 10-Mar 1-Apr HEMB 11-May 11-May 1800 500 11-May A 3000 1600 4-Apr 4-Apr 10-Mar 1-Apr MEMB 13-May 13-May 1600 800 13-May A 3000 700 4-Apr 4-Apr 10-Mar 1-Apr 2_JOBURG 15-May 15-May 1600 400 16-May A 3000 1200 4-Apr 4-Apr 10-Mar 1-Apr WASH 18-May 18-May 1200 600 18-May B 1200 1000 5-Apr 5-Apr 5-Mar 1-May CUT 20-May 20-May 1000 900 10-May B 1200 600 5-Apr 5-Apr 5-Mar 1-May 2_JOBURG 12-May 13-May 13-May 600 350 12-May B 1200 400 5-Apr 5-Apr 5-Mar 1-May WASH 14-May 14-May 400 250 14-May

As it can be seen here following the example:

 Mon-09-May Tue-10-May Wed-11-May Thu-12-May Fri-13-May Sat-14-May Mon-16-May Wed-18-May Thu-19-May Fri-20-May Sat-21-May 1400 600 500 500 500 300 800 800 200 400 400 400 400 600 600 800 100 350 250 250 150

8.Order A the sequence of loading is following the dept sequence that is CUT,HEMB,MEMB,2_JOBURG,WASH
9.So every dept is starting 2 after the previous dept even though we have capacity to load.
10.Order B is starting the CUT on 800 even the minimum is 900 because the remaining capacity to plan is 1400 - 600 which is 800. So even the min mins advised it shall not respect based on the remaining capacity as here shown.
11.Coming to the formula:
12.I have been able to build this formula based on several help receive from the internet and VBAEXPRESS
13.the IF(OR(\$AF60="",\$AJ60=""),0 , Checks if the WIP is blank or the MIN MAX Column is blank then to place 0.
14.IF(AY\$58<\$AJ60,0 this part is suppose to check the start date from the calendar row (shadow_Normal_Calc_Calendar_Row),if the date is less than the date on the calendar then to place 0.
14.IF(AY\$58>=\$AJ60,IF(0<(\$AN60),MIN((\$AN60-SUM(AX60:\$AX60)),\$AO60 : this suppose to take into consideration the min mins declare and to laod acccordingly until the full qty is loaded.
15.SUMIF(Shadow_Dept_Lines_Sum_Col,\$AM60,AY\$4:AY\$56)-SUMIF(\$AM\$58:\$AM59,\$AM60,AY\$58:AY60): is suppose to check if the capacity hs been met and if so then use the the next column for calculation.

for info: this analysis take us almost 1 full day and with 5 people sitting together. As its very time consuming to do manually.

We have been able to compute it but this is the problem actually as for 1 change we have to wait for almost 2 mins as there are other calculation being done and this is the part taking much more time than expected.  Reply With Quote

17. if its not well visible thanks to inform me i will attached it on a file.  Reply With Quote

18. To snb
I'm replacing:
```            mysum = 0
For i = 1 To colm - 1  'do this by slicing and dicing arrays a la snb instead.
mysum = mysum + resultsAry(rw, i)
Next i```
with:
```            mysum = 0
If colm > 1 Then
With Application
mysum = .Sum(.Index(resultsAry, rw, .Transpose(Evaluate("row(1:" & colm - 1 & ")"))))
End With
End If```
Both seem to give the same results.
2 questions:
1. Is there a slicker way?
2. So far it seems a lot slower - is this your experience?  Reply With Quote

19. @p45cal;

Not using Excel functions in VBA seems to be always faster.

Since you sum all values per 'row' you could try to reduce the code to:

```If colm > 1 Then
With Application
mysum = .Sum(.Index(resultsAry, rw))
End With
End If```
Basically:

```Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

For j = 1 To UBound(sn)
MsgBox Application.Sum(Application.Index(sn, j))
Next
End Sub```  Reply With Quote

20. Originally Posted by snb Since you sum all values per 'row' you could try to reduce the code to:
Not quite, it sums the sheet-equivalent of only the cells to the left on the same row as the cell in question, but not including that cell's value.  Reply With Quote

#### Posting Permissions

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