# Calculation order

#### Geoff Taylor

##### Active Member
I have a large model that manipulates data from multiple sources using all sorts of SUMPRODUCTS, SUMIFS and V/HLOOKUP formulas. It works just fine.

I've written code to automatically generate all the required formulae, sensitive to the size of the data tables and various other factors. The code's logic was to turn off calculation, produce all the formulae and turn calculation back on again, but Excel repeatedly crashed when attempting to calculate the finished model at the end of the routine. However, when I debugged the code line-by-line and forced a calculation every line there was no crash and everything was just dandy.

I've resorted to inserting calculation instructions at a few points during the code and this now works. It's slower than before but at least it doesn't crash, but I'm struggling to understand why this is necessary. Could someone shed some light?

Here's a snippet of the code...
<code>
*SNIP*

With Worksheets("Costings")

.Range("D15:" & sEndColumn & "15").FormulaR1C1 = "=IF(ISNA(VLOOKUP(LEFT(R[-2]C,FIND(""/"",R[-2]C)),'Program Code Rules'!R2C3:R73C4,2,FALSE))=TRUE,""??"",VLOOKUP(LEFT(R[-2]C,FIND(""/"",R[-2]C)),'Program Code Rules'!R2C3:R73C4,2,FALSE))"
.Range("D16:" & sEndColumn & "16").FormulaR1C1 = "=VLOOKUP(RIGHT(R[-3]C,1),'Program Code Rules'!R3C8:R8C9,2,FALSE)"
.Range("D20:" & sEndColumn & "20").FormulaR1C1 = "=SUMIF(Banner!R2C19:R50000C19,R13C,Banner!R2C43:R50000C43)"

*SNIP*
</code>

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This could purely be to do with the volume of data it is crunching through. You could be doing 50,000+ vlookups across 6 columns (so 300,000 sums) across massive ranges on different sheets or workbooks. it takes a lot to process that.

I have found I have this issue on a number of my projects where it can take 30min to process a set of calculations.

I have personally found it is quicker to run the first set of calculations then copy and paste special the result before starting the next. I usually do this a column or 2 at a time. You don't have the audit trail but it makes the spreadsheet more manageable.

Another solution which I have seen on here is to use the macro to loop through the cells and and do each cell individually (I haven't applied this to mine yet but it should be even quicker).

I'm sure that sheer volume has something to do with it, but I just wondered if there was anything else.

I can't use pasting values because part of the model is "what-if" analysis and so I need it to be dynamic. It's a trick to consider on other models, though. Thanks

Replies
0
Views
223
Replies
4
Views
606
Replies
1
Views
486
Replies
4
Views
431
Replies
1
Views
255

1,203,696
Messages
6,056,764
Members
444,891
Latest member
MelissaBr

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

### Which adblocker are you using?

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

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