I'm trying to create a subroutine to perform an analysis.
There are two declared ranges. I want to randomly draw three of the values from the results range, perform returns analysis, and pass this result to one cell of the results range. I then want to repeat this until the second, larger range is populated with this random draw analysis.This is the code I have so far:
Sub boostrap()
Dim returns As Variant
Dim Results As Variant
Dim n As Integer
Dim m As Integer
Dim ret As Variant
Dim firstrow As Variant, lastrow As Variant
Dim annualised_return As Variant
Dim i As Long, j As Long
returns = Range("returns")
Results = Range("Results")
n = Range("returns").count
m = Range("Results").count
lastrow = Range("returns").End(xlDown).Row
firstrow = Range("returns").Row
For j = 1 To m
ReDim draw(1 To 3) 'to create a draw reference number from the second range
draw(1) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)
draw(2) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)
draw(3) = Application.WorksheetFunction.RandBetween(firstrow, lastrow)
ReDim ret(1 To 3)
ret(1) = returns(draw(1).Value)
ret(2) = returns(draw(2).Value)
ret(3) = returns(draw(3).Value)
Results(j) = annualised_return = (((1 + ret(1)) * (1 + ret(2)) * (1 + ret(3))) ^ (1 / 3) - 1)
Next j
End Sub
Stepping through the sub, the error seems to stem from when i try and pass the numbers from the draw as a reference to the results range. i.e if the draw produces #22, then i want to select the value in row 22 to be placed in the array of results, which i will then analyse (along with the other two draws) to populate each cell of the results range.
Can anyone provide assistance on how to remedy this? Muchos thanks
Aucun commentaire:
Enregistrer un commentaire