PDA

View Full Version : Loop inside a function to retrieve data



sfb123
10-09-2017, 06:30 PM
I am trying to have a function that calculates de Variance of returns (ln(return2/return1)^2) of the S&P in order to have it cumulatively added and subtract it from a "budget" number.
Once this substraction becomes equal or larger than "budget" I want it to provide me the Date in which day that happens ( on column A)
So it looks like this
Column A Column B Column
01/30/2015. 2550.......................
01/29/2015. 2600 ................Date where cumulative variance is larger or equal than Budget......
............
04/25/2014. ....................................
I have written the following code but I fail to obtain the desired result:


Public Function strange_sum(inrange As range, budget As Double) As Long
Dim total As Long
Dim i As Double
Dim w As Double

i = 8
total = 0

While total < budget
i = i + 1
var = Log(Cells(i, 8).Value / Cells(i + 1, 8).Value)
total = total + var

If i = 2 And total < 0 Then total = 0



strange_sum = Cells(i, 9).Offset(0, -1)

Wend
End Function
I need this for work :/ id appreciate all your help.
Thanks! ( its my first post)

mdmackillop
10-10-2017, 04:46 AM
Can you provide a workbook sample? Go Advanced / Manage Attachments