Identify top three maximum unique values in a range using VBA

[DonEB]

I'm attempting to place code I currently have within a worksheet into VBA and, simply put, I'm not sure how to accomplish this.

Currently, I have a range of data (Quarterly Schedule (D53:W72)) which I've been able to determine what the 3 largest unique values are using the following functions within the worksheet (in Cells AI53, AI54 and AI55, respectively):

1. [LIST=1]
   [*]=LARGE(D53:AG72,1)
   [*]{=MAX(IF(D53:AG72<AI53,D53:AG72))}
   [*]{=MAX(IF(D53:AG72<AI53,D53:AG72))}
   [/LIST]
   [*]

While the above does work, it does fall short of what I need (see below) and I'm still not sure how to put similar code within VBA to accomplish this.

The range of Quarterly Schedule (D53:AG72) is the largest range I need to test, this is required when I'm searching for the max unique value involving 20 players.

The issue becomes a little more complex when I need to find the 3 largest unique values for a range that is less than the maximum.
Example:

1. The starting point will always be D53.
2. I can predetermine the number of players involved. In this example 8 players (this value is established in a separate worksheet called ALLWEEKS:G79)
3. I can sort all data to place "active" players information up front (i.e., D53:K60)

I don't know how to dynamically change the AG82 down to K60 using the value contained in ALLWEEKS:G79 nor can I figure out how to replace the array formula I originally used with something that will work within VBA. Is this possible?

Thank you for any guidance you can provide.
Don

 

[DanteAmor]

The formulas should be like this:

varios 30dic2023.xlsm
	AI
53	99
54	98
55	97
Hoja1

Cell Formulas
Range	Formula
AI53	AI53	=LARGE(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79),1)
AI54	AI54	=MAX(IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI53,D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)))
AI55	AI55	=MAX(IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI53,IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI54,D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))))
Press CTRL+SHIFT+ENTER to enter array formulas.

With VBA:

Sub Macro2()
  Dim n As Long
  
  n = Range("ALLWEEKS!G79").Value
  
  Range("AI53").Value = Evaluate("=LARGE(D53:INDEX(D53:AG82," & n & "," & n & "),1)")
  Range("AI54").Value = Evaluate( _
    "=MAX(IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI53,D53:INDEX(D53:AG82," & n & "," & n & ")))")
  Range("AI55").Value = Evaluate( _
    "=MAX(IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI53,IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI54," & _
    "D53:INDEX(D53:AG82," & n & "," & n & "))))")
End Sub

 

[DonEB]

The formulas should be like this:

varios 30dic2023.xlsm
	AI
53	99
54	98
55	97
Hoja1

Cell Formulas
Range	Formula
AI53	AI53	=LARGE(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79),1)
AI54	AI54	=MAX(IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI53,D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)))
AI55	AI55	=MAX(IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI53,IF(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79)<AI54,D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))))
Press CTRL+SHIFT+ENTER to enter array formulas.

With VBA:

Sub Macro2()
  Dim n As Long
 
  n = Range("ALLWEEKS!G79").Value
 
  Range("AI53").Value = Evaluate("=LARGE(D53:INDEX(D53:AG82," & n & "," & n & "),1)")
  Range("AI54").Value = Evaluate( _
    "=MAX(IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI53,D53:INDEX(D53:AG82," & n & "," & n & ")))")
  Range("AI55").Value = Evaluate( _
    "=MAX(IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI53,IF(D53:INDEX(D53:AG82," & n & "," & n & ")<AI54," & _
    "D53:INDEX(D53:AG82," & n & "," & n & "))))")
End Sub

Thanks for sharing examples in both Excel and VBA format. I tested out the VBA code and it works just as I had hoped it would. Thank you very much for you help.