Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop till value changes | Excel Discussion (Misc queries) | |||
Copy cell contents in empty rows below it till any unempty row com | Excel Discussion (Misc queries) | |||
count rows till cell is blank | Excel Programming | |||
delete dulipate rows by checking each and every cell in a row till end. | Excel Programming | |||
macro to loop till end of page | Excel Programming |