Using VBA to determine Maximum Values in an Excel Spreadsheet

[DonEB]

I’ve run into a problem and don’t have a clue as to what I may be doing wrong.

Using VBA, I’m attempting to determine a number of Maximum and Minimum values in a very specific range of data. Here are some specifics about this range of data:

• The data is located in a worksheet called CommonData and
• The data would exist between E5:X24.
• In the logic below, while using the Index function, I use the variable “n” to help limit the size of the chart that is to be examined. Note: “n” is passed along based on the number of players being used.

Focusing on determining Maximum Values, I am attempting to use the following logic to determine the Highest and second highest values. You will find this in a subrouting called Max:

Dim wsName2 As String
Dim n As Long
wsName2 = “CommonData”
'n = nPlayers    Note:  nPlayers is a variable that is dynamically set based on the number of players being examined.  In this example, I am simply setting the variable to 12
n = 12

With Sheets(wsName2)
     Max1 = Evaluate("=LARGE(E5:INDEX(E5:X24," & n & "," & n & "),1)")
     Max2 = Evaluate( "=MAX(IF(E5:INDEX(E5:X24," & n & "," & n & ")<Max1,E5:INDEX(E5:X24," & n & "," & n & ")))")
End With

Applying the above formula to the chart below, I get the following results and I haven't a clue as to where those results are coming from.
Max1 = 5 <==== Exactly what I was expecting
Max2 = 0 <==== was expecting the second highest or 2

Any thoughts on what I may be doing wrong?

 

[Artik]

Why can't it be in a simpler way?

 Max1 = Evaluate("=MAX(E5:X24)")
 Max2 = Evaluate("=LARGE(E5:X24),2")

Artik

 

[DonEB]

Why can't it be in a simpler way?

 Max1 = Evaluate("=MAX(E5:X24)")
 Max2 = Evaluate("=LARGE(E5:X24),2")

Artik

Thank you for the question. Unfortunately, I do not have a short answer. Hopefully, the following will provide a little insight into why I do not believe I can take the simpler root.

This VBA code I'm attempting to fix is used within a program that is being created to schedule 4, 8 or 12 players to one, two or three tennis courts, respectively. In this example, all 12 players are examined to determine which combination might be best to be placed on the first court. Because of this, the attribute "n" is set to the value of 12. Once the first four players have been examined, then the remaining 8 players are considered for the second court (i.e., n = 8). At this point, the chart is updated who remains to be eligible to play and is resorted to place the remaining 8 players at the beginning of the chart (horizontally and vertically).

Hopefully, you can now see how the "n" variable dynamically changes as players are being considered for court assignment. And by limiting the range of values I'm looking at, I am able to determine the NEW MAX values based on only those remaining players.

 

[DonEB]

Just wanted everyone to know that I figured out the solution to this problem.

The old (wrong) line of code was like this:

Max2 = Evaluate( "=MAX(IF(E5:INDEX(E5:X24," & n & "," & n & ")<Max1,E5:INDEX(E5:X24," & n & "," & n & ")))")

The correct way to write this code was as follows:

Max2 = Evaluate( "=MAX(IF(E5:INDEX(E5:X24," & n & "," & n & ")<" & Max1 & ",E5:INDEX(E5:X24," & n & "," & n & ")))")

Basically, I had to move the variable Max1 outside the quotes. Once I did this, the code work as expected.

 

[Artik]

Your last two posts confirm the principle that verbalizing a problem (whether verbally or in writing) often leads to a solution. That's why you can sometimes find rubber duckies on programmers' desks, with whom they talk about problems.

Artik