Top x values in a column - need VBA Help

[Mike]

Hello all,

I'm writing a program that requires me to total up the top
x values in a column of 50 numbers. "x" is a floating
value based on the user entry on the UI. The VB code will
then take that entry and scan the column for the
corresponding values. For example, the user says they
need the top two values from this list:

5
7
4
2
9

I need the total returned to show 16 (7+9). If they say
they need the top 3, I need 21 (7+5+9).

Hopefully this is clear. I'd appreciate any help that
someone may be able to offer,

Thanks in advance and have a great day,

Mike

 

[Dave Peterson]

You could do it with a worksheet formula:
I put the numbers in a1:a50 and my input in B1

=SUM(LARGE(a1:a50,ROW(INDIRECT("1:"&b1))))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And if I needed a macro, I'd use that same formula:

Option Explicit
Sub testme99()

Dim myRng As Range
Dim wks As Worksheet
Dim myNumber As Long
Dim TotalNumbers As Long

Set wks = Worksheets("sheet2")
With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "a").End(xlUp))
End With

TotalNumbers = 0
On Error Resume Next
TotalNumbers = myRng.Cells _
.SpecialCells(xlCellTypeConstants, xlNumbers).Count
On Error GoTo 0

If TotalNumbers = 0 Then
MsgBox "No numbers in Range"
Exit Sub
End If

Do
myNumber = Application.InputBox _
(Prompt:="How many from: 1 to " & TotalNumbers, _
Type:=1)
If myNumber <= TotalNumbers Then
Exit Do
End If
Loop

If myNumber <= 0 Then
Exit Sub
End If

MsgBox wks.Evaluate("sum(large(" _
& myRng.Address & ",row(1:" & myNumber & ")))")

End Sub