Consulting

Results 1 to 10 of 10

Thread: Problem using arbitrary column length in a user defined function

  1. #1

    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,

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    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)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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,

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Why VBA. Select the sheet and do it in Excel.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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,

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    [vba]

    Activeworkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!$A$1,,,COUNTA(temp3!$A:$A),1)"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    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

  8. #8
    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,

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    My mistake, I din't use R1C1 notation

    [vba]

    ActiveWorkbook.Names.Add Name:="rngPop", RefersToR1C1:="=OFFSET(temp3!R1C1,,,COUNTA(temp3!C1),1)"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    DONE!!!

    Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •