How to get the maximum value ignoring the text ?

[karmapala]

I've searched on the internet, but I couldn't find the vba MAX with a case
where the values to be found as max value is something like this "PA-190909xxx".
The "190909" is based on a formatted current date "yymmdd"

Sometimes the user need to know what is the last invoice number of current date.

For example, in the "DATA" worksheet the invoice numbers are :
PA-190909134
PA-190909142
PA-190909143
PA-190909171
PA-190909120

So he wants the code to show him a message box,
something like "The last invoice number of today is PA-190909171"
rather than he select the sheets DATA, then search the rows to find the latest invoice number.

What I've tried :

Please Login or Register  to view this content.

Which of course it doesn't work.

So how is the code to get that kind of message box ?

Any kind of respond would be greatly appreciated.
Thank you in advanced.

[nigelog]

try

Please Login or Register  to view this content.

[nigelog]

apologies

Please Login or Register  to view this content.

[sintek]

something like this "PA-190909xxx".

Please Login or Register  to view this content.

[bulina2k]

Hi there karmapala,

If you care to use a non-VBA solution, use this array formula:

Please Login or Register  to view this content.

This is an array formula so enter it using CTRL+SHIFT+ENTER.

[Fluff13]

Maybe

Please Login or Register  to view this content.

although this won't work if you have blank cells in the range

[jindon]

Please Login or Register  to view this content.

[sintek]

jindon's code just rocks...

Please Login or Register  to view this content.

[jindon]

So he wants the code to show him a message box,
something like "The last invoice number of today is PA-190909171"

Please Login or Register  to view this content.

[nigelog]

Just noticed invoice number is for the current date, no idea if resets to 1 for the next day but taking that into account

Please Login or Register  to view this content.

[jindon]

Hummm, for the month...

Please Login or Register  to view this content.

[karmapala]

Wow... thank you for the codes, nigelog, sintek (and the attachment file), bulina, fluff13 and jindon.
The code from all of you work well.

However I have to choose...
so I choose jindon's code.

Please Login or Register  to view this content.

Thank you once again guys.
I really appreciate it.

[Fluff13]

You're welcome & thanks for the feedback

[Rick Rothstein]

Please Login or Register  to view this content.

Interesting and surprising to me... the single sheet reference before the short-form Evaluate function call propagates to the two ranges inside. Is doing it this way better than the way I always thought it had to be done?

MsgBox [max(if(left(data!c1:c10000,3)="PA-",substitute(data!c1:c10000,"PA-","")+0))]