Loop Till the end of used Cell

[hon123456]

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

 

[carlo]

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

 

[Nigel]

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