Loop Till the end of used Cell
I wouldn't use select, that slows your procedure down.
try this:
For Each cell In ActiveWorkbook.Worksheets("sheet1").Range("A1",
Range("A1").End(xlDown))
If cell.Value = TextBox1.Text Then
cell.Offset(0, 1).Value = "Yes"
flag = True
Count = Count + 1
TextBox1.Text = ""
Exit Sub
End If
Next cell
I am not sure what you need flag and count for, but that doesn't
matter that much.
Right now the code would stop at an empty cell, i would do following:
For Each cell In ActiveWorkbook.Worksheets("sheet1").Range("A1",
Range("A65536").End(xlup))
(Pay attention to the wordwrap, above should be on one line!!!)
then you could also add
application.screenupdating = false
(do not forget to reset it to true after the code is finished!!!)
hth Carlo
On Jan 7, 3:31*pm, hon123456 wrote:
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
|