Return the 95th biggest value from a array of 100

    Re: Return the 95th biggest value from a array of 100


    Freddy,


    Have a look at this code and see if it helps at all.


    Code
    Public Sub a()
    Dim arr(1 To 10)    As Integer
    Dim i               As Integer
    For i = 1 To 10
        arr(i) = i ^ 2
        Debug.Print i, arr(i)
    Next i
    Debug.Print Application.Large(arr, 3)
End Sub


    I have assumed that you are in VBA, if not you can use the Large and Small functions on your worksheet

    Code
    =Large(A1:A100,95)


    Alan.

    Re: Return the 95th biggest value from a array of 100


    Thanks alot,
    but this is a array I have defined in VB. So the values has not been written to the spreadsheet yet. I've only populated the array in my macro.


    I would like to avoid writing the values to the spreadsheet. It takes up to much time.


    ????

    Re: Return the 95th biggest value from a array of 100


    Hi Freddie,


    How about using the function within VBA?


    Like this:[vba]Sub Test()
    Dim varNos As Variant

    varNos = Array(1, 22, 2, 33, 3, 44, 4, 55)
    MsgBox Application.WorksheetFunction.Large(varNos, 4)

    End Sub[/vba]HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login