Can someone help me to condense this VBA code - it takes a long time to compute

**shayej** · 05-11-2023, 05:04 AM

I am trying to carry out a sensitivity analysis, result are presented in a table like format below:

SAtable.png

Thank you for your help.

Sub SASC()
'
'

    Application.ScreenUpdating = False
    
    Sheets("Sensitivity Analysis").Range("r12:w17").Copy
    Sheets("Sensitivity Analysis").Range("r4").PasteSpecial Paste:=xlPasteValues
          
    
'first line

' sale -5% and cost -5
    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r5").Copy
    Sheets("Rev + Cost").Range("e26").PasteSpecial Paste:=xlPasteValues
    
    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("s5").PasteSpecial Paste:=xlPasteValues

    
' sale -2.5% and cost -5
    Sheets("Sensitivity Analysis").Range("T4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("T5").PasteSpecial Paste:=xlPasteValues

' sale same and cost -5
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("u5").PasteSpecial Paste:=xlPasteValues

' sale 2.5% and cost -5
    Sheets("Sensitivity Analysis").Range("v4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("v5").PasteSpecial Paste:=xlPasteValues
    
    
' sale 5% and cost -5
    Sheets("Sensitivity Analysis").Range("w4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("w5").PasteSpecial Paste:=xlPasteValues
  
    

'second line

' sale -5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r6").Copy
    Sheets("Rev + Cost").Range("E26").PasteSpecial Paste:=xlPasteValues
    
    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("s6").PasteSpecial Paste:=xlPasteValues

    
' sale -2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("T4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("T6").PasteSpecial Paste:=xlPasteValues

' sale same and cost -2.5
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("u6").PasteSpecial Paste:=xlPasteValues

' sale 2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("v4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("v6").PasteSpecial Paste:=xlPasteValues
    
    
' sale 5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("w4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("w6").PasteSpecial Paste:=xlPasteValues
  


'third line

' sale -5% and cost 0
    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r7").Copy
    Sheets("Rev + Cost").Range("E26").PasteSpecial Paste:=xlPasteValues
    
    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("s7").PasteSpecial Paste:=xlPasteValues

    
' sale -2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("T4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("T7").PasteSpecial Paste:=xlPasteValues

' sale same and cost same BASE POSITION
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("u7").PasteSpecial Paste:=xlPasteValues

' sale 2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("v4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("v7").PasteSpecial Paste:=xlPasteValues
    
    
' sale 5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("w4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("w7").PasteSpecial Paste:=xlPasteValues
  

'fourth line

' sale -5% and cost -5
    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r8").Copy
    Sheets("Rev + Cost").Range("E26").PasteSpecial Paste:=xlPasteValues
    
    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("s8").PasteSpecial Paste:=xlPasteValues

    
' sale -2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("T4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("T8").PasteSpecial Paste:=xlPasteValues

' sale same and cost same
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("u8").PasteSpecial Paste:=xlPasteValues

' sale 2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("v4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("v8").PasteSpecial Paste:=xlPasteValues
    
    
' sale 5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("w4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("w8").PasteSpecial Paste:=xlPasteValues
  

'fifth line

' sale -5% and cost -5
    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r9").Copy
    Sheets("Rev + Cost").Range("E26").PasteSpecial Paste:=xlPasteValues
    
    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("s9").PasteSpecial Paste:=xlPasteValues

    
' sale -2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("T4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("T9").PasteSpecial Paste:=xlPasteValues

' sale same and cost same
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("u9").PasteSpecial Paste:=xlPasteValues

' sale 2.5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("v4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("v9").PasteSpecial Paste:=xlPasteValues
    
    
' sale 5% and cost -2.5
    Sheets("Sensitivity Analysis").Range("w4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Assumptions").Select
    Sheets("Assumptions").Range("D28").ClearContents

    Call CopyUntilZero

    Sheets("Assumptions").Range("D28").Copy
    Sheets("Sensitivity Analysis").Range("w9").PasteSpecial Paste:=xlPasteValues
  
    
   'end
    
    
    Sheets("Sensitivity Analysis").Range("u4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

    Sheets("Sensitivity Analysis").Range("r7").Copy
    Sheets("Rev + Cost").Range("E26").PasteSpecial Paste:=xlPasteValues


    Sheets("Sensitivity Analysis").Select
    Application.ScreenUpdating = True

End Sub

the code for CopyUntilZero is below:

Sub CopyUntilZero()

    Do While Abs(Range("k32").Value) > 0.5
Range("k34").Select
    Selection.Copy
    Range("D28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Loop
    
Application.CutCopyMode = False
Range("d28").Select


End Sub

**JohnTopley** · 05-11-2023, 05:34 AM

Please see yellow banner on how to attach a sample workbook together with an explanation of what you are doing rather than us having to decipher the code.

**shayej** · 05-11-2023, 06:46 AM

Sample worksheet ED.xlsm

Hi I have now attached a simple file.

basically I begin with inserting a value in F3 and E26 in Rev + Cost worksheet.

cashflow sheet calculates cashflow over time.

and when I go to Sensitivity analysis sheet and insert a step size, it changes F3 and E26 values and calculates RV value when cashflow reaches 0 using CopyUntilZero.

i hope this explains the steps properly.

**Artik** · 05-11-2023, 07:01 AM

The first thing I see that slows down the execution of the code is using the clipboard to copy data, for example, instead of

    Sheets("Sensitivity Analysis").Range("r12:w17").Copy
    Sheets("Sensitivity Analysis").Range("r4").PasteSpecial Paste:=xlPasteValues

you can write

    shSA.Range("R4").Resize(6, 6).Value = shSA.Range("R12:W17").Value

and instead of

    Sheets("Sensitivity Analysis").Range("s4").Copy
    Sheets("Rev + Cost").Range("F3").PasteSpecial Paste:=xlPasteValues

use

    shRC.Range("F3").Value = shSA.Range("S4").Value

You can also abandon Select, which also significantly slows down the operation.
Below is the code after improvement. I do not exclude that it can be further optimized, but this is a job not for me.

Sub SASC_1()
    Dim shSA As Worksheet
    Dim shRC As Worksheet
    Dim shA As Worksheet
    

    Application.ScreenUpdating = False
    
    Set shSA = ThisWorkbook.Sheets("Sensitivity Analysis")
    Set shRC = Sheets("Rev + Cost")
    Set shA = Sheets("Assumptions")
    
    'shSA.Range("R12:W17").Copy
    'shSA.Range("R4").PasteSpecial Paste:=xlPasteValues
    shSA.Range("R4").Resize(6, 6).Value = shSA.Range("R12:W17").Value

    'first line

    ' sale -5% and cost -5
    'shSA.Range("S4").Copy
    'shRC.Range("F3").PasteSpecial Paste:=xlPasteValues
    shRC.Range("F3").Value = shSA.Range("S4").Value

    shRC.Range("E26").Value = shSA.Range("R5").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("S5").Value = shA.Range("D28").Value

    ' sale -2.5% and cost -5
    shRC.Range("F3").Value = shSA.Range("T4").Value
    
    Call CopyUntilZero_1(shA)

    shSA.Range("T5").Value = shA.Range("D28").Value

    ' sale same and cost -5
    shRC.Range("F3").Value = shSA.Range("U4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("U5").Value = shA.Range("D28").Value

    ' sale 2.5% and cost -5
    shRC.Range("F3").Value = shSA.Range("V4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("V5").Value = shA.Range("D28").Value


    ' sale 5% and cost -5
    shRC.Range("F3").Value = shSA.Range("W4")

    Call CopyUntilZero_1(shA)

    shSA.Range("W5").Value = shA.Range("D28")


    'second line

    ' sale -5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("S4").Value
    shRC.Range("E26").Value = shSA.Range("R6").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("S6").Value = shA.Range("D28").Value

    ' sale -2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("T4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("T6").Value = shA.Range("D28").Value

    ' sale same and cost -2.5
    shRC.Range("F3").Value = shSA.Range("U4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("U6").Value = shA.Range("D28").Value
    
    ' sale 2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("V4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("V6").Value = shA.Range("D28").Value

    ' sale 5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("W4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("W6").Value = shA.Range("D28").Value


    'third line

    ' sale -5% and cost 0
    shRC.Range("F3").Value = shSA.Range("S4").Value
    shRC.Range("E26").Value = shSA.Range("R7").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("S7").Value = shA.Range("D28").Value

    ' sale -2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("T4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("T7").Value = shA.Range("D28").Value

    ' sale same and cost same BASE POSITION
    shRC.Range("F3").Value = shSA.Range("U4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("U7").Value = shA.Range("D28").Value

    ' sale 2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("v4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("V7").Value = shA.Range("D28").Value

    ' sale 5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("W4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("W7").Value = shA.Range("D28").Value


    'fourth line

    ' sale -5% and cost -5
    shRC.Range("F3").Value = shSA.Range("S4").Value
    shRC.Range("E26").Value = shSA.Range("R8").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("S8").Value = shA.Range("D28").Value

    ' sale -2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("T4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("T8").Value = shA.Range("D28").Value

    ' sale same and cost same
    shRC.Range("F3").Value = shSA.Range("U4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("U8").Value = shA.Range("D28").Value

    ' sale 2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("V4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("V8").Value = shA.Range("D28").Value

    ' sale 5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("W4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("w8").Value = shA.Range("D28").Value


    'fifth line

    ' sale -5% and cost -5
    shRC.Range("F3").Value = shSA.Range("S4").Value
    shRC.Range("E26").Value = shSA.Range("R9").Value


    Call CopyUntilZero_1(shA)

    shSA.Range("S9").Value = shA.Range("D28").Value

    ' sale -2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("T4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("T9").Value = shA.Range("D28").Value

    ' sale same and cost same
    shRC.Range("F3").Value = shSA.Range("U4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("u9").Value = shA.Range("D28").Value

    ' sale 2.5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("V4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("V9").Value = shA.Range("D28").Value

    ' sale 5% and cost -2.5
    shRC.Range("F3").Value = shSA.Range("W4").Value

    Call CopyUntilZero_1(shA)

    shSA.Range("W9").Value = shA.Range("D28").Value


    'end

    shRC.Range("F3").Value = shSA.Range("U4").Value
    shRC.Range("E26").Value = shSA.Range("R7").Value

    shSA.Select
    Application.ScreenUpdating = True

End Sub



Sub CopyUntilZero_1(wks As Worksheet)
    
    With wks
        .Range("D28").ClearContents

        Do While Abs(.Range("K32").Value) > 0.5
            .Range("D28").Value = .Range("K34").Value
        Loop

    End With

End Sub

Artik

**shayej** · 05-11-2023, 08:36 AM

Thank you so much, it did make the whole process much faster.

**shayej** · 05-11-2023, 09:37 AM

it used to take 1 min to finish the analysis now it takes just under 8 seconds

Can someone help me to condense this VBA code - it takes a long time to compute

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can someone help me to condense this VBA code - it takes a long time to compute

Re: Can someone help me to condense this VBA code - it takes a long time to compute

Re: Can someone help me to condense this VBA code - it takes a long time to compute

Re: Can someone help me to condense this VBA code - it takes a long time to compute

Re: Can someone help me to condense this VBA code - it takes a long time to compute

Re: Can someone help me to condense this VBA code - it takes a long time to compute

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Code Takes too long to Run

[SOLVED] how to set up progress display for code that takes a long time

[SOLVED] This code takes way to long to run

[SOLVED] code ignore different spelling and takes a long time

[SOLVED] Translate code takes long time

The runtime for my code I wrote takes too long, is there a way tocan you condense?

Long code takes an age to run

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions