Seeking VBA help for Multi column Minimum code

[CanadianAccessUser]

How do I go about adjusting the below Minimum() module code to find the 5 lowest marks on the test, not just THE lowest?... it needs to grab the lowest, then look past the lowest for others with the same mark, then for the second lowest and the third lowest etc...
Many times an employee will score 0 in 3-4 different places and at this point it's only returning the first one it comes across. If the employee gets 0 in three places, as well as a 25% and 33% I need it to return them all: 0, 0, 0, .25, .33

Here is my code for the lowest mark:

Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the smallest.
 
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal
 
End Function

OR

How do I go about adjusting my code to skip the lowest mark and find the second so I can create/use 5 different Minimum codes to find the required data?

Any help would be appreciated.

 

[plog]

No offense, but if you were able to write that code, the leap to finding the 5 lowest isn't that hard. What are you having difficulty with?

In general, instead of an integer variable to hold the lowest value, you would need an array of integers (let's call it retArray) to hold the lowest values. Keep track of the highest value in retArray, if an element of the array you are looking for is less than that highest value, replace the highest value in retArray with that value.

Again, I'm at a loss as to why you aren't asking a specific question if you were able to produce the code you posted.

 

[CanadianAccessUser]

I'm new to VBA and typically find codes on the internet and adjust them.... this one was originally made by dbDamo and started as a Max code, I adjusted it to meet my needs but just don't know enough about VBA to finish the job. I learn new things about VBA every day and with every project, but my knowledge is still painfully lacking...
How would I add this array of integers exactly?
Looks like I'll be needing some VBA training so I'm not driving you guys nuts...

 

[plog]

Do you understand the code you have posted? (that's not a passisive-aggressive, rhetorical question--I'm not trying to sound like an A-hole, I genuinely want to know if you understand what and how your code is working). If so, its not that difficult to make it work for the lowest 5.

You would add an array (retArrray) at the beginning to hold the lowest 5 values.
You would put the first 5 values of the FieldArray in retArray.
You would order retArray from lowest to highest (retArray(4) would have largest value)
Then you would loop through the rest of the elements in FieldArray and for each one:
-test it against retArray(4), if smaller, keep testing it against retArray elements until you find where it goes.
- if it belongs in retArray, you put it in correct position, so that retArray(4) is always the largest value in retArray

Once your loop is done you have the 5 lowest values in FieldArray. Give it a shot, research VB arrays and post back here any specific issues you are having.

 

[CanadianAccessUser]

I understand about half of what you said, but I'll do some research and get back to you when I need more help. Thank you so much for your help and patience.

 

[CanadianAccessUser]

Do you understand the code you have posted? (that's not a passisive-aggressive, rhetorical question--I'm not trying to sound like an A-hole, I genuinely want to know if you understand what and how your code is working).

I really don't... and I'm honestly lost trying to get this thing to do what you've suggested... :banghead:

Can you break it down in a For Dummies format for me? lol

 

[plog]

That was the dummies version, the only other option is have someone write it for you. Sorry, I'm not that person. I'll hold your hand as you try, but I'm not writing that code.

 

[CanadianAccessUser]

Fair enough... lol

 

[CanadianAccessUser]

Would it be easier for a VBA beginner if we adjusted the minimum code to skip the smallest and give the second smallest? I know I would end up with 5 minimum codes, but that seems easier on my brain when it comes to placing the values in the query and even tho I understand the directions above I just plain don't know how to write it.
What do you think?

 

[namliam]

How about using a select statement?

Select Top 5 EmployeeID, EmployeeScore from yourtable order by EmployeeScore asc

 

[CanadianAccessUser]

Thank you namliam,

If I'm not mistaken, this code will give me 5 employees with the lowest overall grade on the test. What I'm looking for is one employee's 5 lowest scoring sections in the test...
Any ideas?

 

[namliam]

You dont have scores as rows?

 

[CanadianAccessUser]

nope, it's set up this way:

Agent Name : Opening : Confidentiality : Email : Tenure : Ownership : etc...

 

[namliam]

Then you are back to Plog's description

 

[CanadianAccessUser]

I've done some research on VBA and I've come up with this:

Function Minimum2(ParamArray RetArray() As Variant)
 
' Declare the local variables.
Dim I As Integer
Dim currentVal0 As Variant
Dim currentVal1 As Variant
 
' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(RetArray)
If RetArray(I) < currentVal0 Then
currentVal0 = RetArray(0)
End If
Next I
For I = 0 To UBound(RetArray)
If RetArray(I) >= currentVal0 < currentVal1 Then
currentVal1 = RetArray(1)
End If
Next I
 
' Return the second smallest value found.
Minimum2 = currentVal1
 
End Function

I've figured out how to get the second lowest instead of the lowest, only thing is, it's giving me the second largest... where did I go wrong?

 

[CanadianAccessUser]

Figured it out! Yay!

I ended up with 5 different modules, but it works!

Here's the one for the second smallest, then I just adjusted for the third and so on....

Function Minimum2(ParamArray RetArray() As Variant)
 
' Declare the local variables.
Dim I As Integer
Dim Smallest As Variant
Dim secondSmallest As Variant
' Set each variable equal to the array of values.
Smallest = RetArray(0)
secondSmallest = RetArray(1)
' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(RetArray)
If RetArray(I) >= Smallest And RetArray(I) < secondSmallest Then
secondSmallest = RetArray(I)
End If
If RetArray(I) < Smallest Then
Smallest = RetArray(I)
End If
Next I
' Return the second smallest value found.
Minimum2 = secondSmallest
 
End Function

Thanks for all the help!