Check if ActiveCell value is greater than the rest in a range

[brainzlp]

I need to test if the activecell range is the bigger in the Activecell.row and column 10 to 20

If activecell is the bigger than use msgbox "aCTIVECELL is the bigger"
else
msgbox "there are bigger values in the range"
end if

Thanks for your help in advance

[Leith Ross]

Hello brainzlp,

In VBA you could use this...

Please Login or Register  to view this content.

[brainzlp]

Hello brainzlp,

In VBA you could use this...

Please Login or Register  to view this content.

Works perfectly, thank you

[Leith Ross]

Hello brainzlp,

You're welcome.

[brainzlp]

Hello brainzlp,

You're welcome.

Sorry to bother you again, although it's not working cuz i have date in a range, is there a way to solve it?

I can use in the range "NUMBER & "º"" , empty, and date.

I just want to analise the numbers

[brainzlp]

The whole range is formatted as Date, which means that empty cells count as " 0 " .
The date count as date (normally)
And the "1º / 2º / 3º / etc etc " although is formatted as date can be analised as text i think.

Sorry for this mess but i really need to have this like this.

Thanks for your help, hopefully to hear from you asap

[easyrhinoman]

Your range seems illogical. Why do you have 3º as a possible value in row 10 to row 20? Aren't you just trying to validate a date being entered?

Perhaps this is an ESL issue; I am familiar with 3º being a temperate, not a valid date.

[easyrhinoman]

I get no errors when running your code on a spreadsheet that looks like this.

Capture.png

[brainzlp]

The numbers indicate the sequence of one order.
Then according tp date date once concluded it gets transformed the number into todays date.

I have all that on separate, i just need to análise if the activecell. Value in the greater of the number ones.

The activecell is always a Number like 1°

Hope i could make myself clear for you
Get back to me asap

[brainzlp]

I get no errors when running your code on a spreadsheet that looks like this.

Attachment 438158

In your example the biggest number should be the 2° although if you put msgbox with application. Max you will see that result isnt what i need

[easyrhinoman]

Still not clear to me. Your english explanation isn't translating well, sorry.

I will try to say it back to you, please tell me if you agree.

You have ten dates in the range.

You also have an ActiveCell that is text, and the text value is "2°"

In plain english, how would you convert "2°" into a date? "2°" is not a logical value for me. I do not intuitively understand the meaning of "2°". In American English, "2°" means 2 degrees of temperature, not a date.

[easyrhinoman]

Why does your cell value have a degree character (this character: °°°°°°°°°°°°°°°°) in it, instead of just the number 1 or the number 2? Why 1° and 2°?

I want to help you, I just don't understand the significance of the inputs here.

[brainzlp]

The °°°° characters in my Country has the same significance as in english the 1st, 2nd, 3rd, 4th, 5th

Thats why i need it.

Isnt there a way to analise the great number ignoring the °°° character?

[brainzlp]

Aboutthe first question i convert it by clear the cell and introducing the date. Although thats done already, just need to analise the numbers followed by °°°.

Which means that in the range there will be sometimes dates, empty cells and numbers with "°°°°°" on them all together.

Let me know if i managed to make my point of view

[YouLookup.TV]

I am presenting this solution because it works, but not because it's a good idea! You should not format a list of checked dates with text... it makes the process much more difficult.

Let me know if this works for you.

Please Login or Register  to view this content.

[brainzlp]

Thanks for your help, although that doesn't work for me and it's too complex.

It gives me an error on:

Please Login or Register  to view this content.

I am looking forward for a quicker answer, cuz this will need to be looped and executed for several filtered rows.

[YouLookup.TV]

It is complex because your requirements are complex. If it were easy, you would already figured it yourself, right? Don't ask the mechanic to fix your engine for you and then question his skills with a wrench.

Change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Then retry.

This code is properly segmented into functions to provide clarity, precision, error-checking, and future scalability. There are many shortcuts in coding, and most of them don't work long-term.

[brainzlp]

I created a simple excel file, about what i have, which i think everyone understand.

What i need is a macro to calculate the maximum value for the number that have the " º " character in the range

Just that, in that case, need a macro to give me the result of "5º" , which is the maximum value of the number with " º " character infront.

[brainzlp]

Thats for your help, still not working properly. Doens't ignoring the rest. Please check my post before, if i clarify my requirements.
Probably using function APPLICATION.DMAX will do the job, with the criteria "º" ?
Thanks in advance for your patience

[YouLookup.TV]

Are you try to find the highest of the TEN cells in the range?

Explain exactly how you plan on using this. The workflow is confusing.

[YouLookup.TV]

This works perfectly for me:

highest value in portuguese ordinal system.xlsm

[brainzlp]

That doesn't work. The result needs to be the highest value of that range (but only the highest of the values with "º" characters)

AS you can see in attach, the result needs to be always 5º,since it's the higher value of the values with "º" character.

Do you understand what i mean ?

[YouLookup.TV]

OK, you need to get a lot better in how you ask questions, because that was never clear at the start.

Replace the old code with this:

Please Login or Register  to view this content.

[YouLookup.TV]

And you don't need VBA for this.

Just enter this formula and press CTRL+SHIFT+ENTER. It will find the highest number that has this character in it: º

=MAX(IF(LEN(SUBSTITUTE(J3:S3,"º",""))<LEN(J3:S3),SUBSTITUTE(J3:S3,"º","")*1,0))

[brainzlp]

still not working... have you tested it ?

Have you understand what i need, it's difficult to explain better ... it's just:

From the values with "º"character in range, calculate the bigger and display that in msgbox.

[YouLookup.TV]

Did you try the formula that I just gave you?

[brainzlp]

That doesn't work. The result needs to be the highest value of that range (but only the highest of the values with "º" characters)

AS you can see in attach, the result needs to be always 5º,since it's the higher value of the values with "º" character.

Do you understand what i mean ?

P.S. I need vba code cuz i need to calculate the maximum value so i do other codes when activecell is the bigger

[YouLookup.TV]

Try this:

Please Login or Register  to view this content.

[YouLookup.TV]

or this if you keep all of my other code

Please Login or Register  to view this content.

[brainzlp]

As you can see it doesn't work...

check attach, the result of the button must be "5º"

[brainzlp]

As you can see it doesn't work...

check attach, the result of the button must be "5º"

P.S. Neither 1 of the solutions work, please try them in the buttons, it displays always "0"

[YouLookup.TV]

OK, this will work given your changing requirements:

Please Login or Register  to view this content.

[brainzlp]

Hello YouLookup.TV , i don't know what i'm doing wrong, or if you're testing differently on another workbook.

IT returns the value "0º" which isn't the max value for the values in the range with "º" character.

You can check in the attach workbook and resend me

[YouLookup.TV]

I opened your file.

You need to delete Module3. You cannot have two macros with the same name.

Then you need to click somewhere in Row 3 first, and THEN click Button 3. The macro is based on the currently active cell.

I successfully see 5º as the highest value when I do these steps in your file and make no other code changes.

[brainzlp]

Seems to be what i want, thanks for your help.

For what i tried, it works, now i need to put this code:

Please Login or Register  to view this content.

(and nothing else of the workbook we were texting about) and see if it will work perfectly.

Tomorrow i will give this a try, i need to go now.

Thanks once again for your time, work and patience to help me

[YouLookup.TV]

Glad to hear it.

[brainzlp]

works perfectly, Thanks

[brainzlp]

Glad to hear it.

Sorry to bother, but now that i need to introduce more columns with "º" symbol.

It considers that 10º is Smaller than 3º for example.

How can i solve that? (Without putting 03 and so on)

Hope you can help me soon

[YouLookup.TV]

Attach an example file. In the example, show me examples of what your file does correctly. Also show me examples of what it doesn't do correctly.

[brainzlp]

MY FILE has a lot of sensitive data, i will took 2 hours just to take all information and macros.

The code you gave me works perfectly for all the rest.

Please Login or Register  to view this content.

The problem occurs in the BOLD line. With your programming, the value 10º is smaller than 3º

Please check if you can work it around that way

[brainzlp]

YouLookup.Tv , please check when you have time, i really need this working cuz i will have more than 10 numbers with "º" symbol