Userform - If value is not found then displays a MsgBox

[maedenegro]

Hi - I was hoping you could help me solve the following.
With the following code I can get a list of values from a Table and displayed on an UserForm; however, I would like to have a msgbox when the value is not found.

-----------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
End If
Next y
End Sub

--------------------------------------------------------------------------------------------

Thank you in advance.

Josue.

 

[ZVI]

Hi and welcome to MrExcel!
Just insert this code line after the Next y


If y > x Then MsgBox "Not found"

And add Exit Sub before End If

 

[Yongle]

which value should be in the message box ????

perhaps one of the two message boxes

Private Sub CommandButton1_Click()
    Dim x As Long
    Dim y As Long
    x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To x
    If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
        TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
        TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
        TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
        TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
        TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
        TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
    Else
        MsgBox Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text & vbCr & "Not found"
        MsgBox TextBox1.Value & vbCr & "Not found"
    End If
Next y
End Sub

 

[ZVI]

One more way


Private Sub CommandButton1_Click()
  Dim x As Long
  Dim y As Long
  Dim IsFound As Boolean
  x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
  For y = 2 To x
    If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
      TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
      TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
      TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
      TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
      TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
      TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
      IsFound = True
    End If
  Next y
  If Not IsFound Then MsgBox "Not found"
End Sub

 

[maedenegro]

You've saved my day! It works.

I appreciate it.