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.match(activesheet.range("A1:A14"), Name,1) gave me trouble sometimes. I think this is because all data in column A is not in alphabetical order.

Public Sub getLastOccuranceOfString() Dim findThis As String 'get value from b1 findThis = Range("b1").Value If Len(findThis) > 0 Then Dim lastOccuranceCellAddress As String Dim lastOccuranceCellRow As Integer For Each c In Range("A:A") Debug.Print "c: " & c & ", findThis: " & findThis If Trim(LCase(c)) = Trim(LCase(findThis)) Then 'get cell row lastOccuranceCellAddress = c.Address lastOccuranceCellRow = c.Row End If If Len(c) = 0 Then 'first occurance of empty cell, assumed to be end of the search list 'exit for loop Exit For End If Next c 'display results If Len(lastOccuranceCellAddress) > 0 Then MsgBox ("The last occurance of '" & findThis & "' is located in row: " & lastOccuranceCellRow) 'select cell Range(lastOccuranceCellAddress).Select Else MsgBox ("'" & findThis & "' does not exist in column A.") End If End IfEnd Sub

Sub kTest() Dim SearchValue As String Dim SearchCol As String Dim SearchRng As String Dim FoundRow As Long Dim LastRow As Long SearchValue = [b1].Value SearchCol = "A" If Len(SearchValue) Then LastRow = Cells(Rows.Count, SearchCol).End(3).Row SearchRng = Cells(1, SearchCol).Resize(LastRow).Address On Error Resume Next FoundRow = Evaluate("match(2,1/(" & SearchRng & "=""" & SearchValue & """))") On Error GoTo 0 If FoundRow Then MsgBox "The last row '" & SearchValue & "' appeared on is Row " & FoundRow Else MsgBox "'" & SearchValue & "' could not found on " & SearchRng End If End IfEnd Sub

Sub Test() Dim ws As Worksheet Dim findRange As Range Dim findCell As Range Dim findWhat As String Set ws = ThisWorkbook.Worksheets("Sheet1") findWhat = ws.Range("B1").Value Set findRange = ws.Range("A1:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row) On Error Resume Next Set findCell = findRange.Find(What:=findWhat, LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows) On Error GoTo 0 MsgBox ("The last row " & findWhat & " appeared on is Row " & findCell.Row & ".")End Sub

Looking back at your original question, see below copied from the help screeen for the MATCH function.

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.):

•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.

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.

Thanks
Rob H

0

ouestqueAuthor Commented:

Awesome tips guys! Thanks so much!

0

Featured Post

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Open in new window