Find last value in column instead of every value

Question

So right now I have this code to find which person is signing in and put NOW=() in column G whenever they use the user form to select their name. The only problem I am having is that if the person is on the sheet previously it replaces all of the row G entries, not just the last one. I only want it to place the now=() function in the G column of the last iteration of the technicians name in the B column.

Visual of description

   Private Sub CommandButton1_Click()

  Dim rngFound As Range
    Dim strFirst As String
    Dim strID As String
    Dim strDay As String

    strID = techname1.Value
    strDay = ""

    Set rngFound = Columns("B").Find(strID, Cells(Rows.Count, "B"), xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do
            Application.Worksheets("SignOut").Cells(rngFound.Row, "G").Value = Now()
            Set rngFound = Columns("B").Find(strID, rngFound, xlValues, xlWhole)
        Loop While rngFound.Address <> strFirst
    End If

    Set rngFound = Nothing

    Unload Me


End Sub

Mrig · Accepted Answer · 2016-06-26 17:00:32Z

2

To get the last occurrence of technicians name you do not have to loop through the column. Try below code:

Private Sub CommandButton1_Click()
    Dim rngFound As Range
    Dim strFirst As String
    Dim strID As String
    Dim strDay As String

    strID = techname1.Value
    strDay = ""

    'below line will give you the last occurrence of strID
    Set rngFound = Columns("B").Find(strID, Cells(Rows.Count, "B"), xlValues, xlWhole, , xlPrevious)
    If Not rngFound Is Nothing Then
        Application.Worksheets("SignOut").Cells(rngFound.Row, "G").Value = Now()
    End If

    Set rngFound = Nothing

    Unload Me
End Sub

Syntax of Find function is:

Range_Object.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Here, SearchDirection is an optional parameter that specifies the search direction while searching in the range and can take one of the follwing two values:

xlNext --> search for the next matching value in range
xlPrevious --> search for previous matching value in range

Of these two xlNext is default. When you do not mention it, Find uses xlNext and searches from top to bottom in your case where range is a column. So to get the last occurrence of string use xlPrevious which will search from bottom to top in a column.

edited Jun 26, 2016 at 17:00

answered Jun 26, 2016 at 15:36

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ralph Over a year ago

While the above answer is absolutely correct you still might want to add some minimal explanations. The most significant change (as far as I can tell) in your code is the addition of SearchDirection:=xlPrevious (as part of the Range.Find Method). So, Excel will search row by row starting with the very last row and going up until the first occurrence of strID is found (which is then the last one on the list).

Mrig Over a year ago

@Ralph - Explanation added, thanks for the feedback.

Mrig Over a year ago

@Josh - Did this help?

Collectives™ on Stack Overflow

Find last value in column instead of every value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related