CODEWITHSUNDEEP
vbaexcel
Fabricio Martinez
Fabricio Martinez

Reputation: 27

Find last cell in a column that matches the value

I have a VBA script that searches and finds the value of a cell in a column and then offsets 3 columns to the right and places a timestamp. However, I ran into the problem that it does it for the first match it finds in the column but I want it to do it for the last match instead.

Private Sub ExitButton_Click()

Dim cell As Range
    Set cell = Range("D1", Cells(Rows.Count, 4).End(xlUp)).Find(What:=Me.UsId.Value, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

    If cell Is Nothing Then
        MsgBox "User ID '" & Me.UsId.Value & "' not found! Please enter the ID used while signing in.", vbExclamation
    Else
        cell.Offset(0, 3) = Format(Now(), "hh:mm AMPM")
    End If

    Me.UsId.Value = ""
    Me.UsId.SetFocus

End Sub

I am VERY new to VBA scripting so I am unsure if this is even possible.

Upvotes: 0

Views: 425

Answers (1)

user4039065
user4039065

Reputation:

You just need to start at the top and add SearchDirection:=xlPrevious and After:=[the first cell]. This used to be called tin can looping. Since there is nothing previous to the first cell, it starts at the bottom and looks up.

If you want to look in column D only,

with columns("D")
    Set cell = .cells.Find(What:=Me.UsId.Value, After:=.cells(1), SearchDirection:=xlPrevious, _
                           LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
end with

If cell Is Nothing Then
    MsgBox "User ID '" & Me.UsId.Value & "' not found! Please enter the ID used while signing in.", vbExclamation
Else
    cell.Offset(0, 3) = Now
    cell.Offset(0, 3).numberformat = "hh:mm AM/PM"
End If

Upvotes: 1

Related Questions