Help Cleaning up a recorded macro for a sensitivity analysis

L

liajet24

New Member
Joined
Aug 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I was trying to create a sensitivity table on a cash flow model. Data tables weren't possible because of inputs, calcs and outputs going across multiple tabs. I recorded the following macro and cleaned up a few bits (selections, scrolls etc.). This still seems like a very inefficient method of doing this however, and if you guys have any advice or ways to clean this up, please let me know!

Thanks,

VBA Code: 
Sub IRR_Sensitivity()
'
' IRR_Sensitivity Macro
'

'
    Sheets("Project Inputs").Select
    Range("O698").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-10]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("E6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("E7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("E9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("E10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("O698").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-9]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("F6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("F7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("F8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("F9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("F10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("O698").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-8]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
    Range("O698").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("G7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("G8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("G9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("G10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("O698").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-7]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("H6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("H7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("H8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("H9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("H10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("O698").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-6]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("I6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("I7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("I8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("I9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("J151").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
    Range("J695").Select
    Sheets("Sensistivity Analysis").Select
    Range("D5").Copy
    Range("I10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Project Inputs").Select
    Range("O698").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-8]"
    Range("J151").Select
    ActiveCell.FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
    Range("J695").Select
End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Hi & welcome to MrExcel.
You can start with getting rid of all the selects like
VBA Code: 
Sub IRR_Sensitivity()
'
' IRR_Sensitivity Macro
'

'
   With Sheets("Project Inputs")
      .Range("O698").FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-10]"
      .Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
   End With
   With Sheets("Sensistivity Analysis")
      .Range("E6").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
      .Range("E7").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
      .Range("E8").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
      .Range("E10").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
      .Range("E10").Value = .Range("D5").Value
   End With
End Sub
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
You can start with getting rid of all the selects like
VBA Code: 
Sub IRR_Sensitivity()
'
' IRR_Sensitivity Macro
'

'
   With Sheets("Project Inputs")
      .Range("O698").FormulaR1C1 = "='Sensistivity Analysis'!R[-693]C[-10]"
      .Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-145]C[-6]"
   End With
   With Sheets("Sensistivity Analysis")
      .Range("E6").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
      .Range("E7").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-143]C[-6]"
      .Range("E8").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-142]C[-6]"
      .Range("E10").Value = .Range("D5").Value
      Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-141]C[-6]"
      .Range("E10").Value = .Range("D5").Value
   End With
End Sub
Click to expand...

Oh wow. This just made everything so much better! Thank you so much that was amazing. And I'm loving this site, I hope to brush up my excel skills through here, going to be browsing through seriously after work.

I have two follow up q's:

1. It is only filling out the first column of the table though, is there a way to loop it through the rest without coping each line for columns f-i?
2. Where it says r[-141]c[-6] why are these numbers negatives? I would have thought the origin would be A1?

Thank you so much again!
 
Upvote 0
The origin is the cell that contains the formula so
VBA Code: 
Sheets("Project Inputs").Range("J151").FormulaR1C1 = "='Sensistivity Analysis'!R[-144]C[-6]"
is looking at D7 which is 144 above J151 & 6 columns to the left.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
198
Possum17
P
P
  • Question
Help with Vba Code
Replies
3
Views
162
Coyotex3
C
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
346
Joe4
Joe4
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,093
Latest member
dbomb1414

Share this page

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
Back
Top