excel vba - Using random number draws to repopulate an array for analysis -

i'm trying create subroutine perform analysis.

update: error sub generates follows: run-time error '424' - object required

there 2 declared ranges. want randomly draw 3 of values results range, perform returns analysis, , pass result 1 cell of results range. want repeat until second, larger range populated random draw analysis.this code have far:

sub boostrap()  dim returns variant dim results variant dim n integer dim m integer dim ret variant  dim firstrow variant, lastrow variant dim annualised_return variant dim long, j 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   j = 1 m          redim draw(1 3) 'to create draw reference number 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 3)         ret(1) = returns(draw(1).value)         ret(2) = returns(draw(2).value)         ret(3) = returns(draw(3).value)      results(j) =  (((1 + ret(1)) * (1 + ret(2)) * (1 + ret(3))) ^ (1 / 3) - 1) next j  end sub 

stepping through sub, error seems stem when try , pass numbers draw reference results range. i.e if draw produces #22, want select value in row 22 placed in array of results, analyse (along other 2 draws) populate each cell of results range.

can provide assistance on how remedy this? muchos thanks

there several issues code, of have found if had used option explicit @ top of code module.

firstly, error being caused statement:

returns(draw(1).value) 

draw not declared anywhere, of type variant default; variant, integer after call randbetween(). therefore, doesn't have .value property (or methods or properties matter), calling draw(1).value raises error. should declare draw integer array:

dim draw() integer      'or dim draw(1 3) integer    'better if (1 3), don't need redim 

then don't need .value, use:

ret(1) = returns(draw(1),1) 

note returns 2d variant array due assigning range values. when variant = range().value, makes 2d array, if range 1 cell.

secondly, if want use results variable output data worksheet, need declare range variable , set it, e.g.

dim results range set results = myworkbook.myworksheet.range("results") 

i have added myworkbook , myworksheet because should qualify references in order avoid errors.

thirdly, line:

results(j) = annualised_return = (((1 + ret(1)) * (1 + ret(2)) * (1 + ret(3))) ^ (1 / 3) - 1) 

will test whether annualised_return equal (((1 + ret(1)) * (1 + ret(2)) * (1 + ret(3))) ^ (1 / 3) - 1), , set results(j) equal true/false outcome of test. assume not wanted.

finally, smaller piece of general advice: explicitly declare of variables , avoid declaring variables variant unless there reason it. e.g. firstrow , lastrow both of type long. avoid errors , variant variables less efficient.