Problem using arbitrary column length in a user defined function

Hi everyone, this is my first post. Here I am working with "Bootstrap" method, which is used to non-parametric confidence interval. To do this I need to draw samples from a population.

To do this I used "sample" function, created by Patrick Matthews. However I could not post the exact address, as I need to post at least five threads (ridiculous isnt it?) to do that.

I used that by following way:
Sample(Sheets("temp3").Range("A1:A500"), no, True, False), which is working fine.

But the problem is, my last row, here, A500 is not fixed, i.e length of column is determined by a variable say "no". Therefore I tried following code:

Sample(Sheets("temp3").Range(cells(1,1), cells(no,1))

But unfortunate it is not working.

Can anyone tell me how to solve this problem?

Your help will be highly appreciated.

Regards,
:banghead:

Create a dynamic range name.

Insert>Name>Define... with a name of rngPop, and a Refersto value of

=OFFSET($A$1,,,COUNTA($A:$A),1)

and then use this in the function call

Sample(rngPop, no, True, False)

I did following in purely VBA:

Sheets("temp3").Range(Cells(1, 1), Cells(no, 1)).Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET($A$1,,,COUNTA($A:$A),1)"

But unfortunately, it is not working. I am fetting type mismatch error.

Any solution?

Regards,

Why VBA. Select the sheet and do it in Excel.

Yes I know definitely i can do it in excel. But this calculation is a part of some other calculations which was written in VBA. Therefore I have no other way but to do it in VBA.

Any idea how I can do it in VBA?

Regards,

[vba]

Activeworkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!$A$1,,,COUNTA(temp3!$A:$A),1)"
[/vba]

Really very frustrating for me..........still not working

I did followings:

ReDim port_PL(no) As Double
For i = 1 To no
Sheets("temp3").Cells(i, 1) = ((-2 * Application.WorksheetFunction.Ln(Rnd(1))) ^ 0.5) * Cos(2 * 22 * Rnd(1) / 7)
Next i
ActiveWorkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!$A$1,,,COUNTA(temp3!$A:$A),1)"

port_PL = Sample(rngPop, no, True, False)


However I am getting following error:
compile error:
ByRef argument type mismatch

Actually it is not a problem of that function. It is the problem of assigining name to that range. When I put your formula no name is assigned to that range. However if I assign that name manually and then run that function, then it is working fine.

Therefore what will be correct way to assign a name to that range?

Regards,

My mistake, I din't use R1C1 notation

[vba]

ActiveWorkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!R1C1,,,COUNTA(temp3!C1),1)"
[/vba]

DONE!!! :beerchug:

Thank you very much