Results 1 to 24 of 24

Return 10 largest values using VBA

Threaded View

  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    24

    Return 10 largest values using VBA

    Hi

    I have this sheet where i want to return the 10 largest values in a msgbox.

    e.g. i have

    A B
    Martin 10
    Brian 10
    Kitt 9
    Jens 11
    Peter 1

    and many more. Then i want a msgbox showing me the 10 largest values and the name. Ive tried this code:

    Sub Max10()
    
    Dim rngTestArea As Range, i As Integer, j As Long, MyResult As String
    
    Set rngTestArea = ThisWorkbook.Worksheets("Overblik").Range("C:C")
    
    j = 0
    
    For i = 1 To 10
    
        j = Application.WorksheetFunction.Large(rngTestArea, i)
    
        MyResult = MyResult & Cells(j, 2).Value & " " & " is " & j & vbCr
    
    Next i
    
    MsgBox MyResult
    
    End Sub
    But when im running the code i get a msgbox with e.g

    Jens 11
    Martin 10
    Martin 10 -should have been Brian!
    Kitt 9

    etc.

    Please help =) Thank you!

    Best Regards Martin
    Attached Files Attached Files
    Last edited by Martinbif; 06-29-2013 at 02:35 AM. Reason: Code tags added

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1