Link to home
Start Free TrialLog in
Avatar of V M
V M

asked on

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
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of V M
V M

ASKER

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!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of V M

ASKER

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.
Avatar of V M

ASKER

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)?
Avatar of V M

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of V M

ASKER

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.)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of V M

ASKER

Rgonzo, Thank you! perfect.
Solution provided