Display msgbox with lastrow input

[RvdV16681]

Hello,

I have a userform with a search button. What I would like is a msgbox popup with lastrow input equal to Textbox7.value.
I already have this code for looking up lastrow input that is equal to Textbox7.Value

Private Sub CommandButton6_Click()
Dim I As Long
Dim Lastrow As Long
Lastrow = Sheets("Blad1").Cells(Rows.Count, "A").End(xlUp).row
For I = Lastrow To 1 Step -1
If Sheets("Blad1").Cells(I, 1).value = TextBox7.value Then
MsgBox "Last used by: " & (Sheets("Blad1").Cells(I, 2).value) & vbNewLine & "Expected return date: " & (Sheets("Blad1").Cells(I, 6).value)
Exit Sub
End If
Next

End Sub

The next step before the msgbox is displayed, is that there has to be a value in column 4 on the same row from the last input as well. If that cell is empty the msgbox does not have to popup. Is it possible to add this to the code I already have?

Thanks in advance!!

 

[dmt32]

Hi,
just a guess but see if below does what you want

Private Sub CommandButton6_Click()
    Dim I As Long
    Dim Lastrow As Long
    
    With Sheets("Blad1")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For I = Lastrow To 1 Step -1
            If .Cells(I, 1).Value = TextBox7.Value And Len(.Cells(I, 4).Value) > 0 Then
                MsgBox "Last used by: " & .Cells(I, 2).Value & vbNewLine & _
                "Expected return date: " & .Cells(I, 6).Value
                Exit Sub
            End If
        Next I
    End With
        
End Sub

Dave

 

[RvdV16681]

Hello Dave,

The code does not work, it gives a msgbox popup on every entry that is equal to Textbox7.value, I have the idea that the code does not look if cell 4 on the same last row has a value or not.