Find last value in range

[Todd Huttenstine]

I have in Column A 10 values. Cells A1:A5 have the value "test".
Cells A6:A10 have other values. I would like to return the value "A5"
as this is the last cell in this range to contain the value "test"

I am interested in only returning the last cell that contains my
specified lookup value. Starting from the bottom of the range and
going to the top. I guess this would also be going at it by starting
from the bottom of the sheet looking up. Anyway, any help is
appreciated.


Thanks
Todd

 

[Ron de Bruin]

Try this Todd

Sub Find_Last()
Dim FindString As String
Dim rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub