• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

loop and search


New Member
Hello Experts
I have a macro which identify last used row and then find specific text (Sun) and then Offset columns and put another text over there (Day)
Query: there is loop used and while searching text in specific column and putting value in offsetting column Macro search the phrase multiple time ie number of times last used is. e.g. Last used row is 50th then search function gives results in specific cell but 50 times it runs the loop And if last used row is above 1000 then it will take too much time
I want that loop should run only once till last used row
Please help macro is as follows
>>> use code - tags <<<
Sub Searching()
Dim i As Long
Dim FRow As Range
Dim LastRow As Long

LastRow = Range("I" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1

Set FRow = Cells.Find(What:="Day", After:=ActiveCell, LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False, SearchFormat:=False)
    Selection.Offset(0, -8).Select
    Selection.Value = "Sun"
    Selection.Offset(1, 0).Select

Next i
Loop Until LastRow

End Sub
Last edited by a moderator:


Excel Ninja
Do each row have something to change from Day to Sun ( or opposite )?
What would happen if there would be two Days or Suns per row?
Instead of rows ... should You 'loop' as many times as there are something to find?
case Take time ...
You should take away those Selection 's - those takes time.
As well as - You could stop ScreenUpdating before loops ... and ... after loops ready, set it on again.
Is there formulas in Your sheets - if 'Yes' then - You would do something with Calculation too.


Excel Ninja
You should reread Forum Rules
... and You'll remember soon that there were text about cross-posting,
which You seems to skipped here and that other Forum too