0

I got this code from several references to find the highest and lowest scores:

Then there is something that makes me curious, How do I implement this code in a certain range where I have thousands of data?

Example: The code is run when I want to find the 10 or 30 highest data values in the range B2: B4000 contained in Sheet1, and put the results in sheet 1, range C2?

Function Max(ParamArray values() As Variant) As Variant
   Dim maxValue, Value As Variant
   maxValue = values(0)
   For Each Value In values
       If Value > maxValue Then maxValue = Value
   Next
   Max = maxValue
     End Function

Function Min(ParamArray values() As Variant) As Variant
   Dim minValue, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Next
   Min = minValue
End Function
braX
  • 11,506
  • 5
  • 20
  • 33
Philipe
  • 29
  • 6
  • What "I want to find the **10 or 30 highest data values**" does mean? If there are numbers between 1 and 5000, you need 5000, 498, 480 and so on until 10 or 30? If yes, how to put all of them in a cell? If not, what do you try accomplishing and what range the code should analyze? Your code returns one value, the maximum. – FaneDuru May 13 '21 at 08:37
  • This function only returns the highest value of an array. It doesn't sort the rest of it in an orderly fashion, so it would be hard to implement for this usage. Unless you want to rewrite it completely, to pick out the highest value into a new array, and then create a new array without that value in it, and loop that. You are probably better off with a function that is sorting the array, or just sort the worksheet range. – Christofer Weber May 13 '21 at 08:41
  • You can [sort the array](https://stackoverflow.com/a/152325/9199828) and then pick the 10 or 30 first entries. IF your values are duplicated and you need the highest 10 or 30 unique max values, combine this method with a dictionary. and pick the first/last 10/30 entries of the dictionary – Foxfire And Burns And Burns May 13 '21 at 08:45
  • owh sorry if the question I ask is confusing. I mean is: If I have up to 4000 sales records, I want to find the highest sales value. It's like you when you want to see the top 10 sales that run from January to April .. I hope this is no longer confusing, sorry if I made a mistake. I am confused to find the reference code that matches what I want. or if that is not possible, are there any other solutions? – Philipe May 13 '21 at 09:03
  • 1
    Why not use the inbuilt "max" and "min" function ? – Charlie May 13 '21 at 09:29
  • 1
    Using the `Large` function, which can be applied on an array, too, solves the problem. Please, test the code I posted. – FaneDuru May 13 '21 at 09:43
  • sorry if my knowledge is still limited in this matter. I'm trying to make this line of code using the max function: Sub max() Dim rng As Range Dim dblMax As Double Set rng = Sheet3.Range("K2:K4000") dblMax = Application.WorksheetFunction.max(rng) MsgBox dbMax End Sub But it doesn't display any data, is there something wrong or less than this code I made? – Philipe May 13 '21 at 09:44
  • But you said you do not need the Max value. You need the Top 10 or 30. Isn't this understanding correct? If yes, did you try the code I posted? If your code does not return anything, but no error, this should mean that the range to be analyzed is wrong. Is it "K2:K4000"? You were asking about "B2:B4000"... Also "Sheet1" versus "Sheet3. Does the Sub return an empty MsgBox? – FaneDuru May 13 '21 at 09:54
  • I am using the B2: B4000 range as an example, so that I can learn the code as well .. and the realization of that code, I will use it later in the "K" range. and the explanation you provided really helped me .. sorry once again if my words in the question are a little confusing, but I hope you understand it. :) – Philipe May 13 '21 at 10:57

2 Answers2

0

Try the next code, please:

Sub LargestInRange_array()
  Dim sh As Worksheet, arr, nrR As Long, i As Long
  
  Set sh = ActiveSheet             'use here the sheet you need
  arr = sh.Range("B2:B4000").Value 'put the range in an array
  
  nrR = 5 'the number of Top to be returned (that 10 to 30, in your question)

  'clear the previous returned Top:
  sh.Range("C2:C" & sh.Range("C" & sh.rows.count).End(xlUp).row).ClearContents
  For i = 1 To nrR
    sh.Range("C" & i + 1).Value = WorksheetFunction.Large(arr, i)
  Next i
End Sub

It places as many largest values you set in the variable nrR, starting from "C2".

Edited:

Please, try the version using a function and needing only a range and the Top number. It determines which is the last row in the column to be processed:

Sub testTopXSales()
 Dim sh As Worksheet, rng As Range, arrTop, lastR
  
  Set sh = ActiveSheet                   'use here the sheet you need
  lastR = sh.Range("B" & sh.rows.count).End(xlUp).row 'last row in the range to be processed
                                                                          'adapt "B" to the column you use
  Set rng = sh.Range("B2:B" & lastR)      'use here the range to be processed
  rng.Offset(0, 1).EntireColumn.ClearContents 'clear the clumn to the right contents
  arrTop = TopXSales(rng, 10)              'defining the Top array, using the function
  
  'drop the array content in the next column:
  rng.Offset(0, 1).Resize(UBound(arrTop) + 1, 1).Value = Application.Transpose(arrTop)
End Sub

Function TopXSales(rng As Range, TopNr As Long) As Variant
   Dim arr, arrTop, i As Long, k As Long
   ReDim arrTop(TopNr - 1) 'redim the array to keep the largest value (- 1 because it is a 1D array starting from 0)
   arr = rng.Value              'put the range in an array. It will work with the range itself, but it should be faster so
   For i = 0 To TopNr - 1    'creating the Top array
        arrTop(k) = WorksheetFunction.Large(arr, i + 1): k = k + 1
   Next i
   TopXSales = arrTop       'make the function to return the Top array
End Function
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • 1
    yes this is what i was looking for :) and it works the way i want it .. Thank you very much for the help, very useful for me. – Philipe May 13 '21 at 11:00
  • @RIDWAN: If you like the code to involve a function returning an array of Top values, according to the imputed range and another parameter, i can also post such a variant. You may learn from it... – FaneDuru May 13 '21 at 11:18
  • yes, I will be happy to study it until I understand. Thank you, the solutions you provided were very useful. – Philipe May 13 '21 at 11:22
  • I'm trying to study the line of code you provide in the sample worksheet I created. but it raises an error on this line : sh.Range("C" & i + 1).Value = WorksheetFunction.Large(arr, i) with notification "unable to get the large property of the worksheetfunction class" – Philipe May 13 '21 at 11:41
  • @RIDWAN: Are there any array created? Did you correctly choose a column having numbers? How did you give value to the `arr` array? Didn't the code work in your initial workbook, neither? – FaneDuru May 13 '21 at 11:47
  • sorry if i was a bit of a bother, and also for the late response .. As you might expect, the code that you are currently providing works very well. it turns out that what I entered was not a number in the range, but after I changed it to numeric format, all the code that you provided worked perfectly as I expected. – Philipe May 13 '21 at 14:36
0

Hy maybe it will help you out

Private Sub hy()
    
    Dim foo As Object
    Set foo = test(3)
    
    Dim i As Integer
    For i = 0 To foo.count - 1
        Debug.Print foo(i)
    Next i
    
End Sub


Function test(count As Integer) As Object

    Dim arr As Object
    Set arr = CreateObject("System.Collections.ArrayList")
    
    arr.Add 70
    arr.Add 30
    arr.Add 60
    arr.Add 50
    arr.Add 200
    arr.Add 10
    arr.Sort
    
    Set test = CreateObject("System.Collections.ArrayList")
    
    Dim i As Integer
    For i = arr.count - 1 To arr.count - count Step -1
        test.Add arr(i)
    Next i
    
    
End Function
Tóth Zoltán
  • 77
  • 4