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
V MAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Subodh Tiwari (Neeraj)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

Open in new window

You may also try that Function on the sheet like below...
=RowNumberWithHighestValue(A6:A17,"t")

Open in new window

0
Rgonzo1971Commented:
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

Open in new window

Regards
0
V MAuthor 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)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
V MAuthor 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
V MAuthor 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
V MAuthor 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
AlanConsultantCommented:
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
V MAuthor 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
Rgonzo1971Commented:
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)")

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
V MAuthor Commented:
Rgonzo, Thank you! perfect.
0
AlanConsultantCommented:
Solution provided
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.