Myujth

I'm a entry level programmer, mostly all self taught. I have some code which is trying to run through about 2,000,000 scenarios to find the lowest value based on about six various scenarios (age, years at job, investment strategy, etc).

Currently my code is setup :

Sub DetermineAllReturnRequired()

Dim wsSummary As Worksheet: Set wsSummary = Worksheets("Summary")
Dim wsLowestReturn As Worksheet: Set wsLowestReturn = Worksheets("LowestReturn")

'Loop variables
Dim i As Double, j As Long, k As Long, c As Long, g As Long, w As Integer, a As Integer, y As Integer, b As Integer

'Variables same for both grades
Dim Interest As Double
Dim Years As Integer
Dim Age As Integer
Dim Found(0 To 1) As Range
Dim Arr_HighestSavings(0 To 1, 0 To 12, 20 To 40, 1 To 7, 0 To 1, 0 To 1, 0 To 1) As Double ' (c - continuation, k - years served, j - year retired, a - intersect options (60, 65, 70, 75, 80, 85, 90), g = grade, w - withdraw 4%, b - adjusted for inflation)

Application.ScreenUpdating = False

'Clears values
If wsLowestReturn.Cells(4, 2).Value <> Null Then
 For j = 20 To 40
 For k = 0 To 12
 For g = 0 To 1
 For c = 0 To 1
 For w = 0 To 1
 For b = 0 To 1
 For a = 1 To 7
 wsLowestReturn.Cells((k * 9) + (3 + a), j - 18 + c * 52 + g * 26 + b * 104 + w * 208).Value = Null
 Next a
 Next b
 Next w
 Next c
 Next g
 Next k
 Next j
End If


'interest
For i = 0.05 To 0.15 Step 0.0001

 wsSummary.Range("G7") = i
 Interest = i

 ' years served when retire
 For j = 20 To 30

 wsSummary.Range("G4") = j
 Years = j

 'current years served
 For k = 0 To 12

 wsSummary.Range("G5") = k
 wsSummary.Range("G15") = k

 'grade (officer,0, enlisted, 1)
 For g = 0 To 1
 If g = 0 Then
 wsSummary.Range("G10") = "Officer"
 wsSummary.Range("G6") = "22"
 Else
 wsSummary.Range("G10") = "Enlisted"
 wsSummary.Range("G6") = "18"
 End If

 'continuation pay (yes, 0, no, 1)
 For c = 0 To 1
 If c = 0 Then
 wsSummary.Range("G17") = "Yes"
 Else
 wsSummary.Range("G17") = "No"
 End If
 'withdrawal rate (0 = yes, no, 1)
 For w = 0 To 1
 If w = 0 Then
 wsSummary.Range("G21") = "Yes"
 Else
 wsSummary.Range("G21") = "No"
 End If



 Set Found(0) = wsSummary.Range("F38")

 If Found(0) = 60 Or Found(0) = 65 Or Found(0) = 70 Or Found(0) = 75 Or Found(0) = 80 Or Found(0) = 85 Or Found(0) = 90 Then
 a = Found(0) / 5 - 11
 b = 0
 If Interest < Arr_HighestSavings(c, k, j, a, g, w, b) Or Arr_HighestSavings(c, k, j, a, g, w, b) = 0 Then
 Arr_HighestSavings(c, k, j, a, g, w, b) = Interest
 wsLowestReturn.Cells((k * 9) + 3, 1) = k
 wsLowestReturn.Cells((k * 9) + (3 + a), j - 18 + c * 52 + g * 26 + b * 104 + w * 208) = Round(Arr_HighestSavings(c, k, j, a, g, w, b), 4)
 End If
 End If

 Set Found(1) = wsSummary.Range("I38")

 If Found(1) = 60 Or Found(1) = 65 Or Found(1) = 70 Or Found(1) = 75 Or Found(1) = 80 Or Found(1) = 85 Or Found(1) = 90 Then
 a = Found(1) / 5 - 11
 b = 1
 If Interest < Arr_HighestSavings(c, k, j, a, g, w, b) Or Arr_HighestSavings(c, k, j, a, g, w, b) = 0 Then
 Arr_HighestSavings(c, k, j, a, g, w, b) = Interest
 wsLowestReturn.Cells((k * 9) + 3, 1) = k
 wsLowestReturn.Cells((k * 9) + (3 + a), j - 18 + c * 52 + g * 26 + b * 104 + w * 208) = Round(Arr_HighestSavings(c, k, j, a, g, w, b), 4)
 End If
 End If
 'MsgBox ("Current years served " & k & " completed!")
 Next w
 Next c
 Next g
 Next k
 Next j
' If i = 0.07 Then
' MsgBox ("at 7")
' End If
Next i
'MsgBox ("Officer Calculations Are Complete")

Application.ScreenUpdating = True

MsgBox ("Calculations Are Complete at " & Now())
End Sub

What I can't figure out is how to speed it up. Currently the code is taking a long time to execute (a day per percent). I'm sure if I can take all the true/false items then it would be faster. Or how to recognize the array value at that j, k, g, c, w, is already determined and skip it.

What the variables are doing is changing values on a different sheet to determine the age at which investments will equal a certain amount.

Thanks for any help you can provide.