Update to a copy + paste as values macro for a model

[Mackers]

Hi

I have a macro that I use on a calculation model that, for each member in my data output, calculates some outputs and pastes the results as values, then moves on to the next member and does the same. The member chosen cycles from 1 to i. The output sheet has a bunch of formulas in row 7 (cells A7, B7, C7 etc. up to AQ7 currently) that refer to named ranges on the model that calculate the results for each member - this way, when I need to add outputs, let's say a new named range called NewOutput, I can just put =NewOutput in cell AR7.

The macro works, but is very slow (15 minutes on my laptop, ~60 minutes on her older model). My senior has asked me to change the macro to not use the InsertRow function, as she thinks it is what's making it slow. I lifted the macro from another workbook, so wondered whether you geniuses could help. The macro is here:

Sub Bulk_run()
'
' Bulk_run Macro
'

'
Application.ScreenUpdating = False

Sheets("Output").Select
Rows("10:64000").Select
Selection.Delete


For i = 1 To 2636

Sheets("Model").Select
Range("IndexNumber").Select
ActiveCell.FormulaR1C1 = i

Sheets("Output").Select
Rows("7:7").Select
Selection.Copy
Rows("10:10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Rows("10:10").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Next i
Application.ScreenUpdating = True
End Sub

 

[davesexcel]

What are the calculations?
If the code goes too fast, excel would not be able to keep up with the calculations.

 

[Mackers]

The short answer is: not too complicated, but the model can't change without significant resource cost, and I don't think I can make it much more efficient.

The longer answer: the calculations involve pulling data from several different tables together, e.g. a Type table that for each member has whether they are "Type A" or "Type B", then using that to find some associated data, e.g. for "Type A" and "Male", factor 1 might be 50%, but for "Type B" and "Female", factor 1 might be 60%. This information is held on a FactorType table, and is all done using index and match. Then there's some basic arithmetic (e.g. MemberValue1 * TypeFactor = Result1) and logic to output flags and run comparisons (check whether Result1 is more than 10% out from Result2, and if so, return a warning flag, then output that flag).

Where the data is member data, stored on one of a few large (several thousand rows and maybe 100 columns) table, I do the MATCH() once in a separate cell, then use the match result to return the data. For most other results the lookups are to a table with at most 50 rows, so should be fairly quick.

 

[davesexcel]

One possible idea would be to do the calculations row by row, instead of calculating then copy the results to a different location.

Then you can just make the row.value=row.value

 

[Mackers]

How would I go about doing that? Sorry, I'm very new to macros - basically I want to calculate all of the outputs for one member, copy and paste them as a value into a row, then move onto the next member. The current macro does this (the formulas in row 7 are pasted in row 10 onwards) but I don't know how to change it to be quicker.

 

[mumps]

Without seeing you actual workbook it's hard to test any possible solution. Try this version of your macro to see if it makes any difference.

Sub Bulk_run()
    Application .ScreenUpdating = False
    Sheets("Output").Rows("10:64000").Delete
    For i = 1 To 2636
        Sheets("Model").Range("IndexNumber").FormulaR1C1 = i
        Sheets("Output").Rows("7:7").Copy
        Rows("10:10").PasteSpecial Paste:=xlPasteValues
        Rows("10:10").Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next i
    Application .ScreenUpdating = True
End Sub

 

[davesexcel]

Can you give a sample of the formulas?
I assume the index # is used for the formulas and you are referencing it in row 7 formulas.

 

[Mackers]

Sorry, I can't provide the sheet, and thanks for your help on this both of you. I can provide a bit more info; there's a "Model" tab that has all the calculations and a "Data" tab that has a whole bunch of tables with member data, factors, price indices and so on. You input an index # in the "Model" tab (that's what "i" loops through) and then there are quite a lot of cells that from this, pull together all the member information, factors, and starting values to the "Model" tab. Then there are a bunch of calculations using this data further down on the "Model" tab that produce outputs, which are called things like Output_Value1 and Output_Value2.

The "Output" sheet is just headers "Output_Value1", "Output_Value2", "Output_Flag1" etc and formulas =INDIRECT($A6), INDIRECT($B6) that pull through whatever named range I input into the headers. Is using INDIRECT() slower than just using =Output_Value1, =Output_Value2, =Output_Flag1, =Output_Flag2? That could make a difference.

Mumps, I tried your macro and it works correctly but isn't noticeably quicker, so I'm guessing the earlier response was right in that the calculations are what the issue is, not the InsertRow part of the macro. I think I just have to suck it up as there isn't any budget to spend time optimising the calculation. Thanks for your help.

 

[mumps]

You are very welcome. A high number of formulas will slow the macro down. The macro could turn calculations off at the beginning and back on at the end but I don't think that would work for you because, if I understood correctly, you want to do the calculations for each member and then copy paste them, one at a time.