# Help writing a loop within loop in VBA

#### pplstuff

##### Well-known Member
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

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.

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:
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.

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``````

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!

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

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?

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?

Replies
1
Views
107
Replies
4
Views
254
Replies
4
Views
598
Replies
18
Views
1K
Replies
18
Views
809

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.

### Which adblocker are you using?

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

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