I'm attempting a function that has a loop within a loop, to iterate an optimal value. I'm not seeing why it's not calculating. It returns #value!
On spreadsheet, the following cashflow 100 -500 1500 -600 Option Explicit Option Base 1 Public Function OptimalF(ByVal MyCells As Double) As Double '/Iterates thru possible F values, bound .01 to .99 finding the highest TWR Dim FVariable As Double Dim LargestLoss As Long Dim TWRs(99) As Double Dim nRow As Integer Dim TradeResult As Double Dim Count As Integer Dim RowCount As Integer Dim TWR() As Double Dim MyCells() As Long LargestLoss = Application.WorksheetFunction.Min(MyCells) RowCount = MyCells.Count With MyCells ReDim MyCells(RowCount) As Single ReDim TWR(RowCount) As Double Count = 1 For FVariable = 0.01 To 0.99 Step 0.01 For nRow = 1 To RowCount Step 1 .Cells(nRow, 1).Value = TradeResult TWR(nRow) = 1 + (FVariable * (-TradeResult / LargestLoss)) Next nRow TWRs(Count) = TWR(RowCount) If TWRs(Count) < TWRs(Count - 1) Then OptimalF = FVariable Exit Function End If Count = Count + 1 Next FVariable End With OptimalF = FVariable -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel