Excel 2007 VBA: Finding last row containing specific data
I have an Excel 2007 spreadsheet.
Sheet1 contains random names..
What VBA would I need so that a user can type a name in cell B1. When he/she presses a button, a messagebox appears stating the very last row in column A that contains the name entered in Cell B1. PLEASE SHOW ME A WAY TO DO THIS WITHOUT USING A LOOP.
Example: Pretend I have the data below in column A. (In real life the data varies.)
Row Column A
1 Billy
2 Billy
3 Billy
4 Sally
5 Sally
6 Timmy
7 Joel
8 Eric
9 Clapton
10 Clapton
11 Clapton
12 Clapton
13 Clapton
14 McDougal
If the user typed Eric in cell B1 and pressed the button a messagebox would appear stating: "The last row Eric appeared on is Row 8"
If the user typed Clapton in cell B1 and pressed the button a messagebox would appear stating: "The last row Clapton appeared on is Row 13"
If the user typed Sally in cell B1 and pressed the button a message box would appear stating: "The last row Sally appeared on is Row 5"
I have tried using "Match" to achieve this goal, but it seems that Match does not work sometimes as I think your data has to be in alphabetical order for that method to always work when finding the last row.
e.g., activeworkbook.activesheet
Sheet1 contains random names..
What VBA would I need so that a user can type a name in cell B1. When he/she presses a button, a messagebox appears stating the very last row in column A that contains the name entered in Cell B1. PLEASE SHOW ME A WAY TO DO THIS WITHOUT USING A LOOP.
Example: Pretend I have the data below in column A. (In real life the data varies.)
Row Column A
1 Billy
2 Billy
3 Billy
4 Sally
5 Sally
6 Timmy
7 Joel
8 Eric
9 Clapton
10 Clapton
11 Clapton
12 Clapton
13 Clapton
14 McDougal
If the user typed Eric in cell B1 and pressed the button a messagebox would appear stating: "The last row Eric appeared on is Row 8"
If the user typed Clapton in cell B1 and pressed the button a messagebox would appear stating: "The last row Clapton appeared on is Row 13"
If the user typed Sally in cell B1 and pressed the button a message box would appear stating: "The last row Sally appeared on is Row 5"
I have tried using "Match" to achieve this goal, but it seems that Match does not work sometimes as I think your data has to be in alphabetical order for that method to always work when finding the last row.
e.g., activeworkbook.activesheet
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 CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's a cool trick, Rob.
I vote for Rob's solution unless you absolutely want a VBA approach.
I vote for Rob's solution unless you absolutely want a VBA approach.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looking back at your original question, see below copied from the help screeen for the MATCH function.
Thanks
Rob H
MATCH(lookup_value, lookup_array, [match_type])The MATCH function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):Your section of code sets the match_type to 1 so the data has to be in ascending order. In my suggestion I have set the match_type to 0 so the data can be in any order but will look for an exact match, hence the need for the error check.
•lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
•lookup_array Required. The range of cells being searched.
•match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
The following table describes how the function finds values based on the setting of the match_type argument.
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
Thanks
Rob H
ASKER
Awesome tips guys! Thanks so much!
=IF(ISERROR(MATCH(B1,A1:A1
Thanks
Rob H