Finding the Maximal String, Based on Alphabetic Order

[helivw]

I have a column with data:
AB001
AB002
EF001
EF002
EF003
KE001
KE002
KE003
AB003
The formule which gives me the Maximum String in the Strings starting with EF (the result of my formula => EF003) is:

="EF"&TEXT(MAX((--(LEFT(A1:A9,2)="EF")*(RIGHT(A1:A9,3)))),"000") with [COLOR=#333333]Ctl+Shift+Enter NOT just Enter [/COLOR]

Converting to vba gives:

Range("B1").Value = [="EF"&TEXT(MAX((--(LEFT(A1:A9,2)="EF")*(RIGHT(A1:A9,3)))),"000")]

I try to replace "EF" by a variable: EF_String="EF"

[COLOR=#333333]Range("B1").Value = [=EF_String&TEXT(MAX((--(LEFT(A1:A9,2)=EF_String)*(RIGHT(A1:A9,3)))),"000")][/COLOR]

I get error 2015 as result

Thanks

 

[Rick Rothstein]

You cannot use the square bracket notation if you have variables from the VB world that need to be included into the formula; rather, you must use the Evaluate function (which the square brackets are a short-hand for). Untested, but I think this should work for you...

[table="width: 500"]
[tr]
	[td]Range("B1").Value = Evaluate("""" & EF_String & """&TEXT(MAX((--(LEFT(A1:A9,2)=""" & EF_String & """)*(RIGHT(A1:A9,3)))),""000"")")
[/td]
[/tr]
[/table]

 

[Fluff]

How about

Range("B2").Value = Evaluate("=" & Chr(34) & EF_String & Chr(34) & " &TEXT(MAX((--(LEFT(A1:A9,2)=" & Chr(34) & EF_String & Chr(34) & ")*(RIGHT(A1:A9,3)))),""000"")")

 

[helivw]

Thanks. Appreciate. This will be very helpful

 

[Fluff]

Glad we could help & thanks for the feedback