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
Book1.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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)?
ASKER
As to Rgonzo's formula, it looks like I might get the same result with a bit shorter version?:
res = Evaluate("=MAX(IFERROR(((R IGHT(A6:A1 7,3)))*(LE FT(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.
res = Evaluate("=MAX(IFERROR(((R
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rgonzo, Thank you! perfect.
Solution provided
ASKER
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!!