Loop Till the end of used Cell
 

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

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

Loop Till the end of used Cell
 

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