2

I want my function to select a range as a input. Then to find the top 5 number of the range. After we have the top 5 numbers I need the function to select the number below it. My VBA function can only find the top 5 numbers. I don't know why any of the code is not working. Thank you.

enter image description here

enter image description here

Function Getnum(row) As Integer
Dim Rng As Range, cell As Range
Dim firstVal As Double, secondVal As Double, thirdVal As Double, fourthVal As Double

Set Rng = row
Range("row").Select
Rng("row").Select

firstVal = Application.WorksheetFunction.Large(Rng, 1)
'Rng("firstVal").Select
'Ret1 = Rng("firstVal").Offset(4, 0).Select
'Range("firstVal").Select
'Ret1 = Range("firstVal").Offset(4, 0).Select
'ActivatefirstVal =
'strMyValue = ActiveCell.Offset(4, 0).Value
'Ret1 = ActiveCell.Offset(3, 0).cell("firstVal")

'secondVal = Application.WorksheetFunction.Large(Rng, 2)
'thirdVal = Application.WorksheetFunction.Large(Rng, 3)
'fourthVal = Application.WorksheetFunction.Large(Rng, 4)

MsgBox "First Highest Value is " & firstVal
'MsgBox "Second Highest Value is " & secondVal
'MsgBox "Third Highest Value is " & thirdVal
'MsgBox "Fourth Highest Value is " & fourthVal
MsgBox "First Highest Ret " & Ret1

End Function
braX
  • 11,506
  • 5
  • 20
  • 33
Jared
  • 21
  • 2
  • You might benefit from reading [How to avoid using Select in Excel VBA?](https://stackoverflow.com/q/10714251/4996248) – John Coleman Dec 15 '19 at 20:28

0 Answers0