Help writing a loop within loop in VBA

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
I have a workbook doing sensitivity analysis, which takes a long time to calculate. I'm hoping a macro can do it faster (currently, calculation takes a few hours).

Here's what I need:
-I have a worksheet Sens! and another Inputs!

-I need to enter values 180, 200, 220,..., 320 in Inputs!G4
-I need to enter values 0,1,2,3,...,16 in Inputs!G5
-I need to Copy DCF!C40 and paste it into a matrix that will be on the Sens! sheet (currently, there is no matrix).

The macro would say:
Inputs!g4 becomes 180
Inputs!g5 becomes 0
recalculate workbook
Copy DCF!C40
Paste into Sens!B2

Inputs!G5 becomes 1
Recalculate
Copy DCF!C40
Paste into Sens!C2

Inputs!G5 becomes 2
Recalulate
Copy DCF!C40
Paste into Sens!C3

Once Inputs!G5 = 16 has been calculated, change Inputs!G4 from 180 to 200 and repeat (but paste into B3).

If anyone could help with a marco for this processes I would really appreciate it! It's a loop within a loop, and I'm not sure how to do this (also not sure what the best type is: do for, do while, for, etc.)

Excel 2007
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
See Help for Create a two-variable data table
 
Upvote 0
I can create the data tables. The problem is I have 18 of them. And workbook calculation takes forever. (17*8*18) calculations for data table, whereas using loops I would only have to calculate (17*8) times. And, since it takes a few minutes, to do one iteration, getting rid of the data tables is of importance.
 
Upvote 0
I don't understand where you get your numbers for calculation, or why you believe a macro will be faster than a 2D data table.

In addition, you can set calculation to Automatic except tables
 
Last edited:
Upvote 0
Because with a 2d data table, it will calculate each one (18 of them) one at a time. Wheres, if I have a loop I can simply calculate an iteration, copy 18 relevant cells, paste into 18 different matrices. This will allow me to calculate all 18 data table in the time it takes me to calculate 1 data table currently.

I hope that makes sense, lol. In the OP, I only mention copying 1 variable (in that case IRR). But, there are 17 others I'm interested in - i just figure I can adapt the macro at that point to fit my needs. I've got Walkenbach Power Prog. right next to me and I haven't gotten anything useful out of it, lol.
 
Upvote 0
Hi pplstuff,

I can't quite picture your matrix but this could be a start:

Code:
Sub SensitivityAnalyses()
Dim i As Integer, j As Integer
 
For i = 0 To 16
With ThisWorkbook
Sheets("Inputs").Cells(4, 7) = 180 + 20 * i
Sheets("Inputs").Cells(5, 7) = i
Calculate
Sheets("DCF").Cells(40, 3).Copy Sheets("Sens").Cells(2, 2 + i)
End With
Next i

End Sub
 
Upvote 0
this could be a start:
[/CODE]

Very interesting. I see what it's doing. For ".Cells(2, 2 + i)", will this move down to the next row and start pasting? or will it only go from b2:q2? (as, opposed to b2:q9)

Either way, now that I see it, it is a great start - thank you so much!
 
Upvote 0
Ah, also it's a little off:

The matrix is:
---0|1|2|3|4|5|...|16
180
200
220
240
260
280
300
320
 
Upvote 0
Hi pplstuff

Based on your approach, I came up with this.

Code:
Sub SensitivityAnalysis()
    Dim i As Integer, j As Integer, k As Integer
    For i = 0 To 8
    j = 180 + 20 * i
    With ThisWorkbook
    Sheets("sheet1").Cells(2, 2) = j
        For k = 0 To 16
        Sheets("sheet1").Cells(2, 3) = k
        Calculate
        Sheets("Sheet1").Cells(2, 5).Copy Sheets("sheet2").Cells(2 + i, 2 + j)
        Next k
    Next i
End Sub

I'm doing this in a separate workbook (hence the sheet names). When I run it, I get this error - "Next without For". Any ideas why?
 
Upvote 0
Sorry, for so many posts... I'm almost there, just only issues now is that it is not pasting anything.

Code:
Sub SensitivityAnalysis()
    Dim i As Integer, j As Integer, k As Integer
    For i = 0 To 8
        With ThisWorkbook
        j = 180 + 20 * i
        Sheets("sheet1").Cells(2, 2) = j
        For k = 0 To 16
            Sheets("sheet1").Cells(3, 2) = k
            Calculate
            Sheets("Sheet1").Cells(2, 5).Copy Sheets("sheet2").Cells(2, 2)
        Next k
    End With
    Next i
End Sub

Any ideas why?
 
Upvote 0

Forum statistics

Threads
1,211,454
Messages
6,101,944
Members
447,764
Latest member
gopalgriffith

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