MsgBox popup with last row value

[RvdV16681]

Hello,

I have a userform with a search button. What I would like is a msgbox popup with lastrow input equal to Textbox1.value. Only thing is that the msgbox can only popup if there is no value on column 4 of the same last row. For example, Textbox1.value is 1234 and the last row that is equal to this value is 45. Then if cell D45 has no value, the msgbox should popup, otherwise if there is a value in cell D45 the msgbox should not popup.
I already have this code for looking up lastrow input that is equal to Textbox1.Value

Private Sub CommandButton1_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 = TextBox1.value Then
            MsgBox "In use by: " & (Sheets("Blad1").Cells(I, 2).value) 
            Exit Sub
        End If
    Next

 End Sub

I hope my question if clear enough and I hope it is possible to add this to the code I already have.

Thanks in advance!!

 

[abhi1687]

replace:

If Sheets("Blad1").Cells(I, 1).value = TextBox1.value Then

with this:

If Sheets("Blad1").Cells(I, 1).Value = TextBox1.Value And Sheets("Blad1").Cells(I, 4) = "" The

 

[RvdV16681]

Hello abhi1687,

Thanks for your reply!

This works partially, only thing is that it keeps searching until it finds a row where .Cells(I, 1).Value = TextBox1.Value And Sheets("Blad1").Cells(I, 4) = "" becomes true. Is it possible to stop searching if TextBox1.value is found, even if there is a value in Sheets("Blad1").Cells(I, 4). So stop searching if TextBox1.value is found and only display a msgbox when Sheets("Blad1").Cells(I, 4) = "" on that row.
For example, TextBox1.Value=1234, and this value is on row 45 but also on row 22. Only at row 22 D22 is empty so Sheets("Blad1").Cells(I, 4) = "" is true but at row 45 cell D45 has a value so Sheets("Blad1").Cells(I, 4) = "" is false. Is it possible to stop the search at row 45 and not continue until row 22 is found where Sheets("Blad1").Cells(I, 4) = "" becomes true.

 

[abhi1687]

you can add another if statement within the first one to check value in column D like below:

Private Sub CommandButton1_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 = TextBox1.Value Then
            If Sheets("Blad1").Cells(I, 4) = "" Then MsgBox "In use by: " & (Sheets("Blad1").Cells(I, 2).Value)
            Exit Sub
        End If
    Next
 End Sub

 

[RoryA]

Please do not post the same question repeatedly. Original thread is here: http://www.mrexcel.com/forum/excel-questions/984769-display-msgbox-lastrow-input.html

This one is now closed.