# macro time and calculation issues

#### mtharnden

##### Board Regular
hello, i have a macro that runs a few different modules, 4 to be exact.
the 4th one is a loop pushing in equations to 4 different tabs.
when ran by itself the module only takes a minute or so to run but when ran all together it takes 10 minutes to run (2 minutes per tab and at least 2 to caclulate). the first 3 modules run in under a minute combined and are only opening a file, copying over data, and closing the file. there are no love equations prior to module 4 running.
also to note, if i calculate the equations outside of the macro it only takes a couple seconds.

i have tried calculating one sheet at a time and calculating the whole workbook at the end but doesnt make much a difference.

any idea what would make something take sooooo much longer when ran after other modules than just by itself?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
posting the macros between code tags might help,

how many of the formulas recalculate whilst data is imported

calculations are set to manual. calculations only go at the end of this module. here is the loop, it goes through it on 4 different tabs. all the equatiosn are various sumifs and averageifs with a couple of them having offsets which i know can add to calcuation time.

if i calculate cells right now it will only take seconds, and if i run the module alone just a minute. something is causing the fact that something else runs prior to it to make it take much longer.

now if there is a better way to do this loop that could resolve it, the loop is being used due to the total rows need different equations and there are 3 levels of totals.

Code:
Dim drow As Range

'##Looped eqts
For Each drow In Range("D3:D" & Cells(Rows.Count, "B").End(xlUp).Row)
If Len(drow.Value) > 1 Then
Range("E" & drow.Row) = "Equation"
Range("F" & drow.Row) = "Equation"
Range("H" & drow.Row) = "Equation"
Range("I" & drow.Row) = "Equation"
Range("J" & drow.Row) = "Equation"
Range("K" & drow.Row) = "Equation"
Range("L" & drow.Row) = "Equation"
Range("M" & drow.Row) = "Equation"
Range("O" & drow.Row) = "Equation"
Range("P" & drow.Row) = "Equation"
End If
Next
For Each drow In Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If drow.Value Like "* Total" Then
Range("E" & drow.Row) = "Equation"
Range("F" & drow.Row) = "Equation"
Range("H" & drow.Row) = "Equation"
Range("I" & drow.Row) = "Equation"
Range("J" & drow.Row) = "Equation"
Range("K" & drow.Row) = "Equation"
Range("L" & drow.Row) = "Equation"
Range("M" & drow.Row) = "Equation"
Range("O" & drow.Row) = "Equation"
Range("P" & drow.Row) = "Equation"
End If
Next
For Each drow In Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If drow.Value = "Grand Total" Then
Range("E" & drow.Row) = "Equation"
Range("F" & drow.Row) = "Equation"
Range("H" & drow.Row) = "Equation"
Range("I" & drow.Row) = "Equation"
Range("J" & drow.Row) = "Equation"
Range("K" & drow.Row) = "Equation"
Range("L" & drow.Row) = "Equation"
Range("M" & drow.Row) = "Equation"
Range("O" & drow.Row) = "Equation"
Range("P" & drow.Row) = "Equation"
End If
Next
For Each drow In Range("C3:C" & Cells(Rows.Count, "B").End(xlUp).Row)
If drow.Value Like "* Total" Then
Range("E" & drow.Row) = "Equation"
Range("F" & drow.Row) = "Equation"
Range("H" & drow.Row) = "Equation"
Range("I" & drow.Row) = "Equation"
Range("J" & drow.Row) = "Equation"
Range("K" & drow.Row) = "Equation"
Range("L" & drow.Row) = "Equation"
Range("M" & drow.Row) = "Equation"
Range("O" & drow.Row) = "Equation"
Range("P" & drow.Row) = "Equation"
End If
Next

'other eqts
Range("G3:G" & Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "Equation"
Range("N3:N" & Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "Equation"

Replies
3
Views
674
Replies
0
Views
270
Replies
7
Views
304
Replies
4
Views
233
Replies
2
Views
312

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back