With ActiveWorkbook.Worksheets("sheet1")
For Each cell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
If Trim(cell.Value) = Trim(TextBox1.Text) Then
cell.Offset(0, 1).Value = "Yes"
flag = True
Count = Count + 1
TextBox1.Text = ""
Exit Sub
End If
Next cell
End With


--

Regards,
Nigel




"hon123456" wrote in message
...
Dear all,

I have following code which loop till the end of a column.
But it is rather slow. I want to
change the code that only loop the column till the last row that have
data entered. I do not want it to loop to Range("A1").End(xlDown).
How can I do that?

For Each cell In ActiveWorkbook.Worksheets("sheet1").Range("A1",
Range("A1").End(xlDown))

If cell.Value = TextBox1.Text Then
cell.Offset(0, 1).Select
Selection.Value = "Yes"
flag = True
Count = Count + 1
TextBox1.Text = ""
Exit Sub
End If
Next cell