Results 1 to 6 of 6

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

Threaded View

  1. 05-11-2023, 05:04 AM #1
    shayej
    shayej is offline
    Registered User
    Join Date
    02-04-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

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

    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
    Last edited by shayej; 05-11-2023 at 08:38 AM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Code Takes too long to Run
    By rain4uu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2020, 11:02 AM
  2. [SOLVED] how to set up progress display for code that takes a long time
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2018, 05:14 PM
  3. [SOLVED] This code takes way to long to run
    By Jym396 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2017, 11:07 AM
  4. [SOLVED] code ignore different spelling and takes a long time
    By torti111 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-04-2017, 05:14 AM
  5. [SOLVED] Translate code takes long time
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-28-2016, 12:58 AM
  6. The runtime for my code I wrote takes too long, is there a way tocan you condense?
    By dnice0123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2014, 03:33 AM
  7. Long code takes an age to run
    By E3iron in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-05-2009, 07:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1