Find matching last row and check if the next column row is empty

Question

I have a spreadsheet that includes an ID and a Name. I'd like to have a procedure that (when the user enters a specific ID) will find the most recent instance of that code in the same column, then will check the next column row if its empty. For example:

ID       | Name
SD123456 | John
DF989899 | Alice 
SD123456 | Jason
KA452331 | Wilson
SD123456 | 
DF456790 | Jack

As you can see, the ID 'SD123456' has a missing name, therefore I would like to know if its possible to search for the ID 'SD123456' and get the last row of that ID and check the next column row if its missing. I've tried using xlDown and xlUp but to no avail.

EDIT: In summary, my whole VBA purpose is to search for the ID, then check if that latest ID has a name to it, if not, alert the user that, the ID has a missing name.

Kin Siang · Accepted Answer · 2021-06-02T02:17:14.093

1

You may try this, simple and clear:

Dim lastrow As Long, i As Long
Dim ID As String

lastrow = Sheet1.UsedRange.Rows.Count
ID = "DF989899"

For i = 2 To lastrow
    If InStr(Sheet1.Cells(i, 1).Value, ID) And IsEmpty(Sheet1.Cells(i, 2).Value) Then
      MsgBox "Missing Value for ID: " & Sheet1.Cells(i, 1).Value
    End If
Next

edited Jun 02 '21 at 02:17

answered Jun 02 '21 at 01:28

Kin Siang

2,644
2
4
8

Please note that `End(xlDown)` is not reliable when finding the last row. [This is the proper approach](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Jun 02 '21 at 01:33
@BigBen, ya I got it, especially when 1st row is empty, I encounter this issue once before. But most case, `usedrange` and `end(xlDown)` is sufficient depend on your data structure. – Kin Siang Jun 02 '21 at 01:36
@KinSiang this is interesting but I can foresee a problem, what if there is an entry on Row 7, for example, `DF78901` with a name `Jack`, Row 6 will never be triggered right? Because currently from my understanding, the code only checks for last row – kraljevocs Jun 02 '21 at 01:43
@KinSiang please note that good VBA answers *don't* use `End(xlDown)` or `UsedRange` to find the last row. – BigBen Jun 02 '21 at 01:49
Just put another condition will do, see my edited answer – Kin Siang Jun 02 '21 at 01:49
@BigBen, ya i always prefer `usedrange`, but there is once `end(xldown)` is a good method when the data structure has many empty cells but need to reset the range in the looping, i also feel surprise on it – Kin Siang Jun 02 '21 at 01:51
@KinSiang Thank you, though it's not entirely what I'm looking for but you gave me some ideas, I needed a search function so I think `findEndRow = .Range("A:A").Find(what:="SD123456",after:=.Range("A1"), searchdirection:=xlPrevious).Row` would work? My whole VBA purpose is to search for the ID, then check if that latest ID has a name to it, if not, alert the user that, the ID has a missing name. – kraljevocs Jun 02 '21 at 01:58
If you just want to check ID by refer to any cell or input, you can use my edited answer, hope this time solve your question...`Find` is not good function in VBA, because if no result is found, it will trigger error – Kin Siang Jun 02 '21 at 02:16
@KinSiang Yup this solved my problem, thank you, plus I learnt something new from you too, especially on `Find` – kraljevocs Jun 02 '21 at 02:26
2

@kraljevocs `Find` is perfectly fine to use, *as long as you test if it succeeded*. That can be done by `Dim foundCell As Range`, `Set foundCell = .Range("A:A").Find(what:="SD123456",after:=.Range("A1"), searchdirection:=xlPrevious)`, then using `If Not foundCell Is Nothing Then` to test if the `Find` did indeed succeed. "`Find` is not good" is just wrong. – BigBen Jun 02 '21 at 02:27
1

Welcome, try play around with VBA you can learn more thing :) – Kin Siang Jun 02 '21 at 02:29
@BigBen Ah I see, well that makes sense, having a 'catch' method in an event that the ID could not be found, to prevent compile errors. Thank you! – kraljevocs Jun 02 '21 at 02:31
1

Well it would be a runtime error, not a compile error, but yes. – BigBen Jun 02 '21 at 02:32

score 1 · Answer 2 · answered Jun 02 '21 at 03:15

Find the Last Occurrence of a String in a Column

Option Explicit

Sub CheckIDtest()
    CheckID "SD123456"
End Sub

Sub CheckID(ByVal ID As String)
    
    ' Create references to the ID and Name Column Ranges.
    Dim irg As Range, nrg As Range
    With Sheet1.Range("A1").CurrentRegion
        Set irg = .Columns(1)
        Set nrg = .Columns(2)
    End With
    
    ' Attempt to find the last occurrence of the ID.
    Dim fCell As Range
    Set fCell = irg.Find(ID, , xlFormulas, xlWhole, , xlPrevious)
    
    ' ID was not found.
    If fCell Is Nothing Then
        MsgBox "The ID '" & ID & "' was not found.", _
            vbCritical, "ID Not Found"
        Exit Sub
    End If
    
    ' Write the associated name to a variable.
    ' This complication allows for the columns not to be adjacent.
    Dim fName As String: fName = fCell.EntireRow.Columns(nrg.Column).Value
    ' If they are adjacent like in this case, you could simplify with...
    'fName = fCell.Offset(, 1).Value
    ' ... and forget about 'nrg'. 
    
    If Len(fName) = 0 Then
        MsgBox "The Name for the ID '" & ID & "' is missing.", _
            vbExclamation, "Missing Name"
    Else
        MsgBox "The Name for the ID '" & ID & "' is '" & fName & "'.", _
            vbInformation, "Name Found"
    End If

End Sub

Find matching last row and check if the next column row is empty

2 Answers2

Find the Last Occurrence of a String in a Column