Help writing a loop within loop in VBA

[pplstuff]

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

 

[shg]

See Help for Create a two-variable data table

 

[pplstuff]

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.

 

[shg]

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

 

[pplstuff]

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.

 

[xladept]

Hi pplstuff,

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

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

 

[pplstuff]

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!

 

[pplstuff]

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

 

[pplstuff]

Hi pplstuff

Based on your approach, I came up with this.

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?

 

[pplstuff]

Sorry, for so many posts... I'm almost there, just only issues now is that it is not pasting anything.

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?