# Excel VBA code that returns row number of alphanumeric label ending with highest number

What Excel VBA code can I use to return a row number of the label in column A that starts with "T" and which label ends in the highest 3 digits?
Book1.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
You may try something like this...

``````Function RowNumberWithHighestValue(ByVal rng As Range, letter As String) As Long
Dim cell As Range
Dim High As Long
For Each cell In rng
If LCase(Left(cell.Value, 1)) = LCase(letter) Then
If Replace(LCase(cell.Value), LCase(letter), "") > High Then
High = Replace(LCase(cell.Value), LCase(letter), "")
RowNumberWithHighestValue = cell.Row
End If
End If
Next cell
End Function

Sub Test()
MsgBox RowNumberWithHighestValue(Range("A7:A17"), "T")
End Sub
``````
You may also try that Function on the sheet like below...
``````=RowNumberWithHighestValue(A6:A17,"t")
``````
0
Commented:
Hi,

You could also use a formula

``````Sub Macro()
res = Evaluate("=MAX(IFERROR(--ISNUMBER(--(RIGHT(A6:A17,3)))*(--(LEFT(A6:A17)=""T""))*--(RIGHT(A6:A17,3)),""""))")
MsgBox res
End Sub
``````
Regards
0
Author Commented:
Thank you both Rgonzo and Subodh !; I very much appreciate your ingenuity.  Your responses are intriguing.

I like the brevity of Rgonzo's "evaluate" formula, but it seems to be returning row "7" which contains "T012", and I was aiming to return row "16" which contains "T021"

Subodh's approach returns the desired row "16". However, when I try to evaluate the entire column (A:A) instead of just A6:A17, i get an error.  The range to evaluate (A6:A17 in the example) is variable.

But you have both given me good things to think about.  Thank you!!
0
Excel & VBA ExpertCommented:
You're welcome VM!

Never refer a whole column in any formula or UDF where you are working with criteria otherwise a single cell formula will be checking for the criteria in 1048576 cells.
0
Author Commented:
Indeed.  I would need to first define the top and bottom of column A, and use that range for the function you provided.  Thank you.
0
Author Commented:
Correction to my comment about Rgonzo's reply.  I see that it is returning the maximum value; that is the desired goal.  I just need to convert that maximum result into the row which contains such maximum result.  I should be able to figure that out.   But what also seems very interesting about the "evaluate" VBA function is that it seems to produce the same result as an array formula in Excel (ctrl+enter)?
0
Author Commented:
As to Rgonzo's formula, it looks like I might get the same result with a bit shorter version?:

res = Evaluate("=MAX(IFERROR(((RIGHT(A6:A17,3)))*(LEFT(A6:A17)=""T""),""""))")

I am not sure what the double negatives do, or what the third multiplication does; it seems to produce the same result without them.
0
ConsultantCommented:
Hi,

The double negatives force the formula to return a numeric result all the time, and never a TRUE / FALSE - the latter could give the wrong results under some circumstances.

Most of the time it might not matter, but it is best practice to 'double negate' to be certain.

Just checking - Did you really need the VBA approach?  If not, you could follow the path of RGonzo's approach in a worksheet, and avoid having any VBA in your workbook (unless you have already gotten some, in which case that downside is already present, so more won't make it any worse).

Alan.
0
Author Commented:
Alan, thank you for the explanation about the double negatives.

Regarding the VBA approach, actually the context of my question was that of VBA, i.e., I was creating some VBA code where I needed identify a certain row number.  So a VBA solution is not a problem.  Rgonzo expressed his formula in VBA format.  So I think either suggested approach should work.  (Rgonzo's formula is very helpful, but I would still need to convert the result of that formula into a row number.)
0
Commented:
Hi,

To get the row, pls try
``````res = Evaluate("=MATCH(TEXT(MAX(IFERROR(((RIGHT(A6:A17,3)))*(LEFT(A6:A17)=""T""),"""")),""\T000""),A:A,0)")
``````
Regards
0

Experts Exchange Solution brought to you by