Macro speed - copy & paste calculation loop

chappy

New Member
Joined
Jul 18, 2006
Messages
29
I have created a macro that cycles through a list of input variables from 2 separate tables. It copies input variables into 2 specific cells (C19 & C22). This action calculates a new value in cell C14 and that value is then copied and pasted into a table (E58:O69). Then it loops to the next 2 input variables and continues until it has cycled through and populated the entire table.

The process is extremely simple and it works but it is time consuming. I have several similar macros with the same file and it takes 30 minutes to cycle through all of the calculations. If it is possible to make improvements in the speed it would be extremely helpful.

I tried to search through the questions and content to see if I could find a similar issue. There may be examples in on the board but unfortunately I could not find one that helped me, if any suggestion could be made it would be much appreciated.


VBA Code:
Sub Run_price_capex_sensitivities()
Application.ScreenUpdating = False

'Definie percentages for sensitivities
Set sensPriceRange = Range("D30:D40")
Set sensCapexRange = Range("E22:O22")

'Defintion data paste ranges
Set sensNPVCapexPasteRange = Range("E58:O58")

'Definition cells linked to QVM quick sensitivities
Set Primarysenscell = Range("C19")
Set SecondsensCapexcell = Range("C22")

'define number of rows for price
For n = 1 To 11

'copies primary sensitivity range
sensPriceRange(n, 1).Cells.Copy

'pastes primary sensitivity value to be used in calculation
Primarysenscell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'define number of columns for primary sensitivity
For i = 1 To 11

'copies capex sensitivity of x%
sensCapexRange.Cells(1, i).Copy

'pastes capex sensitivity 
SecondsensCapexcell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'gets NPV from cell C14
Range("C14").Cells.Copy

'Pastes NPV values for Capex sensitivity in row n and columns i
sensNPVCapexPasteRange.Cells(n, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next i
Next n

Application.ScreenUpdating = True

End Sub
 

Attachments

  • Price_Capex.JPG
    Price_Capex.JPG
    125.2 KB · Views: 1

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
First thing to do is add these 2 lines:

VBA Code:
Application.ScreenUpdating = False
Application.Calculation = xlManual
. . .
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
 

chappy

New Member
Joined
Jul 18, 2006
Messages
29
First thing to do is add these 2 lines:

VBA Code:
Application.ScreenUpdating = False
Application.Calculation = xlManual
. . .
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Thanks for your answer diddi

It certainly speeded it up significantly. But unfortunately the removal of a calculation refresh generates a table of identical values.

The issue is that the calculations are made on a separate sheet which is linked to cells C19 and C22. The sequence is:
  1. Updated values in C19 and C22
  2. Calculation refresh to generate a new unique value in C14
  3. Populate the new unique value from cell C14 into the table in E58:O68
The calculation refresh is necessary to generate the unique values.

Thanks again for responding so fast, much appreciated!
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
i would consider a redesign in algorithm, or pass some of the work to VBA to reduce the load in cell calculations which are slow
if you know the critical place where you need to recalculate then you can leave the calculation manual as i had, and insert the line
VBA Code:
Calculate
in the critical place to update everything just the one time
 
Solution

chappy

New Member
Joined
Jul 18, 2006
Messages
29
i would consider a redesign in algorithm, or pass some of the work to VBA to reduce the load in cell calculations which are slow
if you know the critical place where you need to recalculate then you can leave the calculation manual as i had, and insert the line
VBA Code:
Calculate
in the critical place to update everything just the one time

diddi

Thanks for the response. Switching to manual calc and inserting Calculate after each update in the i to n loop has improved the speed by 4 times at first glance.

Many thanks for your help, very much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,394
Messages
5,636,048
Members
416,894
Latest member
Hari1992

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?

Disable AdBlock

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
Top