Max Func returns Variable name

[NTB]

This sub below should return a value of 5, how do get this to return variable Five instead?

Please Login or Register  to view this content.

Please advice!

Thank you,

NTB

[MarvinP]

Hi NTB

Have you looked at the Choose function? No VBA needed. If you really want to put it in VBA you can use Application.WorksheetFunction.Choose...

[NTB]

MarvinP

Choose, I have to pick the item specifically...

I had tried, but the function below return Four... unless I have to put everything in order which in real life the values don't line up like that.

Any other way that you know?

Please Login or Register  to view this content.

[Andrew-R]

It might help if you explained a little more about what you're trying to do and why - having Excel return a variable name rather than the value in the variable is quite an unusual thing to do.

[watersev]

hi, NTB, try this out:

Please Login or Register  to view this content.

returns value 5

[NTB]

Andrew-R:
Basically, what I posted was a simplified version. I didn't want to confuse everyone too much.
My function has 8 different variables, 8 different possible directions: Up, Down, Left, Right, UL, UR, DL, DR

Each variable would hold a dynamic value for each loop and I would ultimately want to pick the variable with the highest value.

Instead of doing so many if statements, I thought of Max fuction, but Max only returns the highest value but not the variable that holds it.

Please Login or Register  to view this content.

To pick DR I would have to do 8 different ifs

if Up > than the rest then Up
if Down > the rest the down
etc etc...

I would love to have something similiar to

Please Login or Register  to view this content.

MaxVar would return "UR" because it holds 6, the highest value

[MarvinP]

Have you looked at the SELECT CASE statement in VBA. It was created for replacing multiple IF statements.
http://www.ozgrid.com/VBA/select-case.htm or
http://www.databison.com/index.php/v...ment-in-excel/

[NTB]

Maybe I'm not understanding this right... but I think it's not relevant to use that here because I would have to compare each variable against the rest.

[Andrew-R]

OK, how about

Please Login or Register  to view this content.

[NTB]

Test run

Please Login or Register  to view this content.

Messagebox returns "UR" - DING DING DING.

Thank you very Much! +1