Finding the Highest value among a set of numbers

[Rabi]

Hello everybody!

I am struggling with an official task, which mainly relies on the Macros.
As I have mentioned in my first query, I am a beginner in Excel and this is the second query.

I have 10 variables bearing 10 different values. I want to find the highest values of them.
I could not find any function like MAX etc. Neither I could put these variables in an array and Sort them in descending order - I could not make out how to define an array and sort it etc.
(How to work with Arrays in VBA will be an additional tip, if possible).

Regards,

[RobynC]

Hi, you said you could not find any function like MAX, but you can use all the worksheet functions in VBA providing that VBA doesn't have it's own equivalent. just type:

worksheetfunction.MAX(Range("A1:A10"))

Obviously change the range to suit. that is of course providing that your values are in a worksheet.

As far as the arrays go, afraid I can't help, although I'm sure I have seen posts on here that deal with sorting arrays.

Regards

[mikerickson]

Where are these 10 values?

If they are scattered in various cells, this is one way to fill an array with those values.

Dim myArray(1 To 10) as Variant

myArray(1) = Range("A12").Value
myArray(2) = Range("B23").Value
myArray(3) = Range("A54").Value
'...
myArray(10) = Range("T32").Value

Application.Max also works on arrays, so sorting is not needed to find the Max value.

MsgBox Application.Max(myArray) & " is the largest."

If there are other reasons for sorting an array, this bubble sort routine will do it.

Dim i As Long, j As Long, temp As Variant

For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If myArray(j) < myArray(i) Then
            temp = myArray(i)
            myArray(i) = myArray(j)
            myArray(j) = temp
        End If
    Next j
Next i

[Rabi]

Great help. All the friends put valuable info, especially on Arrays, into my knowledge.

As I had mentioned that I have different values in 10 variables; like JanAmnt, FebAmnt etc. I like to know, how can I find the highest value in one of these variables.

I would be able to use this feature in a number of other places, in my code.

Thanks in anticipation and regards.

[jindon]

try

MsgBox WorksheetFunction.Max(Array(JanAmnt, FebAmnt, ....))

[Rabi]

Great. Salute to all the wizards. Wish you the best time.

Best regards.

[MickG]

This could be a start:-
Code (1) first loop through the range of cell to initialise all the variables. (The variables are 10 different numbers in Range ("A1:A10")) NB:- You could skip this first bit if you want to assign your variables in a different way It then loops through the Array, by setting the first number as Max and then, if the next number in array is larger than the previous then that becomes the Max .

Private Sub CommandButton1_Click()
Dim n(1 To 10) As Integer, a As Integer
Dim mx As Integer, x As Integer
a = 1
Do Until Cells(a, 1).Value = ""
    n(a) = Cells(a, 1).Value
    a = a + 1
Loop

mx = n(1)
For x = 1 To 10
    If x > 1 Then
        If n(x) > mx Then
            mx = n(x)
        End If
     End If
Next x
MsgBox mx
End Sub

You can also set the Array as a single Variant, variable as shown in the second bit of code, and then use the Worksheetfunction.Max.
NB:- Variant arrays always have two Indices as shown

Private Sub CommandButton2_Click()
Dim n As Variant
n = Range("a1:A10").Value
MsgBox WorksheetFunction.Max(n)
MsgBox n(1, 1) 'This is just to show the first variable
End Sub

I hope this helps
Mick