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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
 
V MAuthor Commented:
Rgonzo, Thank you! perfect.
0
 
AlanConsultantCommented:
Solution provided
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.