ELM616
03-17-2020, 01:40 PM
Hello All,
First time poster and pretty new to VBA. I'm hoping to get some help with printing the results of my VBA loop. Using Excel 2016
I am using a discounted cash flow (DCF) model with three key input variables that can take on a range of values. This results in 336 possible combinations of the three variables. I want to calculate the DCF result for all 336 iterations and print them into a specific range of cells.
The loop works well, but instead of printing 336 unique results from the DCF, it is printing 336 identical results. In fact, it is only the final result, leading me to believe that the final result of the 336th iteration is overwriting all results from itertations 1 through 335.
Below is my code and below that is a sample of the data. Please let me know if you need anymore information. I would greatly appreciate some assistance.
Option Explicit
Sub Iterate()
Dim i, l, m, n, p, k, q As Integer
Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range
Set R1 = Range("IteratedVar1")
Set R2 = Range("IteratedVar2")
Set R3 = Range("IteratedVar3")
Set R4 = Range("IteratedResults")
R1.ClearContents
R2.ClearContents
R3.ClearContents
R4.ClearContents
l = 25
k = 0
q = 7
For m = 1 To Worksheets("Summary").Range("B23")
For n = 1 To Worksheets("Summary").Range("C23")
For p = 1 To Worksheets("Summary").Range("D23")
k = k + 1
Sheets("Summary").Cells(k + l, 1).Value = k
Sheets("Summary").Cells(k + l, 2).Value = Sheets("Summary").Cells(m + q, 2).Value
Sheets("Summary").Cells(k + l, 3).Value = Sheets("Summary").Cells(n + q, 3).Value
Sheets("Summary").Cells(k + l, 4).Value = Sheets("Summary").Cells(p + q, 4).Value
Next p
Next n
Next m
For i = 1 To Worksheets("Summary").Range("L13")
Sheets("Summary").Range("H8").Value = i
Sheets("Summary").Range("B6").Value = Sheets("Summary").Cells(k + l, 2).Value
Sheets("Summary").Range("C6").Value = Sheets("Summary").Cells(k + l, 3).Value
Sheets("Summary").Range("D6").Value = Sheets("Summary").Cells(k + l, 4).Value
Sheets("Summary").Cells(i + l, 6).Value = Sheets("Summary").Range("H9").Value
Next i
End Sub
Discount Rate Variable
Useful Life Variable
Routine Return WACC Variable
30%
5
8.0%
29%
4
7.0%
28%
3
7.0%
27%
2
6.5%
26%
6.0%
25%
5.5%
20%
15%
14%
12%
10%
9%
7%
5%
14
4
6
Count
Discount Rate Variable
Useful Life Variable
Routine Return WACC Variable
DCF Value
1
30.0%
5
8.0%
557,519
2
30.0%
5
7.0%
557,519
3
30.0%
5
7.0%
557,519
4
30.0%
5
6.5%
557,519
5
30.0%
5
6.0%
557,519
6
30.0%
5
5.5%
557,519
7
30.0%
4
8.0%
557,519
8
30.0%
4
7.0%
557,519
9
30.0%
4
7.0%
557,519
10
30.0%
4
6.5%
557,519
11
30.0%
4
6.0%
557,519
12
30.0%
4
5.5%
557,519
13
30.0%
3
8.0%
557,519
14
30.0%
3
7.0%
557,519
15
30.0%
3
7.0%
557,519
16
30.0%
3
6.5%
557,519
17
30.0%
3
6.0%
557,519
18
30.0%
3
5.5%
557,519
19
30.0%
2
8.0%
557,519
20
30.0%
2
7.0%
557,519
21
30.0%
2
7.0%
557,519
22
30.0%
2
6.5%
557,519
23
30.0%
2
6.0%
557,519
24
30.0%
2
5.5%
557,519
25
29.0%
5
8.0%
557,519
26
29.0%
5
7.0%
557,519
27
29.0%
5
7.0%
557,519
28
29.0%
5
6.5%
557,519
29
29.0%
5
6.0%
557,519
30
29.0%
5
5.5%
557,519
First time poster and pretty new to VBA. I'm hoping to get some help with printing the results of my VBA loop. Using Excel 2016
I am using a discounted cash flow (DCF) model with three key input variables that can take on a range of values. This results in 336 possible combinations of the three variables. I want to calculate the DCF result for all 336 iterations and print them into a specific range of cells.
The loop works well, but instead of printing 336 unique results from the DCF, it is printing 336 identical results. In fact, it is only the final result, leading me to believe that the final result of the 336th iteration is overwriting all results from itertations 1 through 335.
Below is my code and below that is a sample of the data. Please let me know if you need anymore information. I would greatly appreciate some assistance.
Option Explicit
Sub Iterate()
Dim i, l, m, n, p, k, q As Integer
Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range
Set R1 = Range("IteratedVar1")
Set R2 = Range("IteratedVar2")
Set R3 = Range("IteratedVar3")
Set R4 = Range("IteratedResults")
R1.ClearContents
R2.ClearContents
R3.ClearContents
R4.ClearContents
l = 25
k = 0
q = 7
For m = 1 To Worksheets("Summary").Range("B23")
For n = 1 To Worksheets("Summary").Range("C23")
For p = 1 To Worksheets("Summary").Range("D23")
k = k + 1
Sheets("Summary").Cells(k + l, 1).Value = k
Sheets("Summary").Cells(k + l, 2).Value = Sheets("Summary").Cells(m + q, 2).Value
Sheets("Summary").Cells(k + l, 3).Value = Sheets("Summary").Cells(n + q, 3).Value
Sheets("Summary").Cells(k + l, 4).Value = Sheets("Summary").Cells(p + q, 4).Value
Next p
Next n
Next m
For i = 1 To Worksheets("Summary").Range("L13")
Sheets("Summary").Range("H8").Value = i
Sheets("Summary").Range("B6").Value = Sheets("Summary").Cells(k + l, 2).Value
Sheets("Summary").Range("C6").Value = Sheets("Summary").Cells(k + l, 3).Value
Sheets("Summary").Range("D6").Value = Sheets("Summary").Cells(k + l, 4).Value
Sheets("Summary").Cells(i + l, 6).Value = Sheets("Summary").Range("H9").Value
Next i
End Sub
Discount Rate Variable
Useful Life Variable
Routine Return WACC Variable
30%
5
8.0%
29%
4
7.0%
28%
3
7.0%
27%
2
6.5%
26%
6.0%
25%
5.5%
20%
15%
14%
12%
10%
9%
7%
5%
14
4
6
Count
Discount Rate Variable
Useful Life Variable
Routine Return WACC Variable
DCF Value
1
30.0%
5
8.0%
557,519
2
30.0%
5
7.0%
557,519
3
30.0%
5
7.0%
557,519
4
30.0%
5
6.5%
557,519
5
30.0%
5
6.0%
557,519
6
30.0%
5
5.5%
557,519
7
30.0%
4
8.0%
557,519
8
30.0%
4
7.0%
557,519
9
30.0%
4
7.0%
557,519
10
30.0%
4
6.5%
557,519
11
30.0%
4
6.0%
557,519
12
30.0%
4
5.5%
557,519
13
30.0%
3
8.0%
557,519
14
30.0%
3
7.0%
557,519
15
30.0%
3
7.0%
557,519
16
30.0%
3
6.5%
557,519
17
30.0%
3
6.0%
557,519
18
30.0%
3
5.5%
557,519
19
30.0%
2
8.0%
557,519
20
30.0%
2
7.0%
557,519
21
30.0%
2
7.0%
557,519
22
30.0%
2
6.5%
557,519
23
30.0%
2
6.0%
557,519
24
30.0%
2
5.5%
557,519
25
29.0%
5
8.0%
557,519
26
29.0%
5
7.0%
557,519
27
29.0%
5
7.0%
557,519
28
29.0%
5
6.5%
557,519
29
29.0%
5
6.0%
557,519
30
29.0%
5
5.5%
557,519