Return 10 largest values using VBA

[Martinbif]

Hi

I have this sheet where i want to return the 10 largest values in a msgbox.

e.g. i have

A B
Martin 10
Brian 10
Kitt 9
Jens 11
Peter 1

and many more. Then i want a msgbox showing me the 10 largest values and the name. Ive tried this code:

Please Login or Register  to view this content.

But when im running the code i get a msgbox with e.g

Jens 11
Martin 10
Martin 10 -should have been Brian!
Kitt 9

etc.

Please help =) Thank you!

Best Regards Martin

[shg]

Why not just sort by score?

[jindon]

Isn't Sorting the range quickest?

Anyway..

Please Login or Register  to view this content.

[FDibbins]

Martinbif Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Martinbif]

Hi Jindon

Thanks for your answer. I am quite new to VBA programming. When i use your code i get this outputCapture.JPG.

I want to illustrate in my msgbox the name of the team and how many wins top 10 has =) Afterwards i want top 10 away wins, etc. Could you please help me understand your code? Thanks! =)

Best Regards
Martin

[jindon]

You have nothing in col.A.

Upload your file rather then uploading picture.

[Martinbif]

Hi Jindon

The file is uploaded now =) Im working in the sheet "overblik" atm =)

Thanks

/ Martin

[jindon]

Change to

Please Login or Register  to view this content.

[Martinbif]

Hi jindon!

Thanks alot. Works almost perfect. Only issue is that i get som random numbers: Capture.JPG

Also could you help me understand the code? Im very new to VBA and would like to understand wich code does what to be able to modify later =)

Thanks alot!

/ Martin

[jindon]

What and where is RANDOM NUMBERS?

[Martinbif]

To the left in my msgbox =)

[jindon]

They are RANK.

Do you want to have top 10 from the Total wins?

Please Login or Register  to view this content.

[Martinbif]

Arh perfect... i understand now... it is because it dont fill the rank in the msgbox when they are the same =)

thanks alot!

[Martinbif]

Hi Jindon =)

A quick question.. How do i modify the msgbox to only show one decimal when returning %? e.g. 31,1 % instead of 0,311234383493 ?

Thanks in advance =)

[jindon]

Please Login or Register  to view this content.

[Martinbif]

Thanks =)

How to make it general? work on all values if more than one? I mean how to make 0.311.... general for the whole msgbox? =)

Thanks

[jindon]

I don't understand what you are asking and how is it related to your original question?

The forum allows only one question per thread.

[Martinbif]

It is related because it is related to the output of my original question?

What you showed my was to make one value into %... As you saw in my sheet i got many values.. So im asking how to make the msgbox so it applys to all instead of a single value =)

But nwm if i had to make a new thread then i must do that apperently

/ Martin

[jindon]

As I said, I don't understand what you are asking.

[Martinbif]

Recall the msgbox from earlier Capture.JPG

Imagine that there are decimals instead of integers... Then im asking how to make them all into % instead of e.g. 0,453232121 and not only one number from earlier 0,31.... =)

[jindon]

So, how is it related to RANK?

Your question is really vague.

Show me the example with your desired result in a workbook.

[Martinbif]

Capture.JPG

As you can se on the picture all the values are in decimals, i want them to be in % =)

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Martinbif]

Thank you very much =)