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

user_11007863 0
Viewed 43

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.

vba excel
2 Answers

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
user_158978610

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
user_98140690
Related