MsgBox popup with last row value

Status
Not open for further replies.

RvdV16681

Board Regular
Joined
Mar 31, 2016
Messages
62
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

Code:
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!!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

abhi1687

Board Regular
Joined
Mar 2, 2013
Messages
110
replace:
Code:
If Sheets("Blad1").Cells(I, 1).value = TextBox1.value Then

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

RvdV16681

Board Regular
Joined
Mar 31, 2016
Messages
62
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.
 
Last edited:

abhi1687

Board Regular
Joined
Mar 2, 2013
Messages
110
you can add another if statement within the first one to check value in column D like below:
Code:
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
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,123,124
Messages
5,599,839
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top