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:

Please Login or Register  to view this content.

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

[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 .

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

I hope this helps
Mick

[mikerickson]

Where are these 10 values?

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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

Please Login or Register  to view this content.

[Rabi]

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

Best regards.