Loop and search

[tpa]

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
Regards,
Please help macro is as follows
------------------------------------
Sub Searching()
Dim i As Long
Dim FRow As Range
Dim LastRow As Long

LastRow = Range("I" & Rows.Count).End(xlUp).Row
Do
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)
FRow.Activate

Selection.Offset(0, -8).Select
Selection.Value = "Sun"
Selection.Offset(1, 0).Select

Next i
Loop Until LastRow

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Do you want to search column I for the word "Day" & put "Sun" into col A?
Also is the word Day on it's own in the cell, or is it part of a larger string?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: loop and search
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[tpa]

Hi & welcome to MrExcel.
Do you want to search column I for the word "Day" & put "Sun" into col A?
Also is the word Day on it's own in the cell, or is it part of a larger string?

Thanks for reply, my requirement is to search in column I word "Sun" and put "Day" in col A and yes "Sun" is the part of the larger string, and it appears only once in a row but repeated number of times in col I

 

[tpa]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: loop and search
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

sorry for inconvenience as I was not aware of Cross posting the question in other forum, will definitely remember to mention link if posted for future reference and also follow other forum rules.

 

[Fluff]

Ok, how about

Sub tpa()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Offset(, -8).Value = Evaluate("if(isnumber(search(""day""," & .Address & ")),""Sun""," & .Offset(, -8).Address & ")")
   End With
End Sub

 

[tpa]

Thanks for quick updates this will serves the purpose but one more requirement comes in those row where there is no "Day" phrase there it appears 0 (Zero) in Col A

 

[Fluff]

Ok, how about

Sub tpa()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Offset(, -8).Value = Evaluate(Replace("if(isnumber(search(""day""," & .Address & ")),""Sun"",if(@="""","""",@))", "@", .Offset(, -8).Address))
   End With
End Sub

 

[tpa]

Ok, how about

Sub tpa()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Offset(, -8).Value = Evaluate(Replace("if(isnumber(search(""day""," & .Address & ")),""Sun"",if(@="""","""",@))", "@", .Offset(, -8).Address))
   End With
End Sub

Thanks a lot for solution

 

[Fluff]

You're welcome & thanks for the feedback.