Use a boolean variable and set the value to true if found. Also exit the loop
if found

Private Sub CommandButton1_Click()

Dim i As Integer
Dim blnFound As Boolean
Rng = Cells(Sheet1.Rows.Count, "A").EndXlUp.Row
For i = 1 To Rng
If Cells(i, "A") = Me.TextBox1.Text Then
Me.TextBox2.Text = Cells(i, "B").Address
blnFound = True: Exit For
End If
Next

If blnFound < True Then MsgBox "Value does not exists in your sheet"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"tkraju via OfficeKB.com" wrote:

I am testing a range whether a value exists or not.If does not exists prompt
a msg else give me cell address.
My code is:
Private sub CommandButton1_Click()
Dim i as integer
Rng=Cells(Sheet1.Rows.Count,"A").EndXlUp.Row
For i= 1 to Rng
If Cells(i, "A")=Me.TextBox1.Text Then
Me.TextBox2.Text=Cells(i, "B").Address
End If
Next
End Sub
I need a Msg"Value does not exists in your sheet".Where I should put line of
code to get the desired results.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1