Loop and search

tpa

New Member
Joined
Mar 19, 2013
Messages
6
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2013
Messages
6
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

New Member
Joined
Mar 19, 2013
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

New Member
Joined
Mar 19, 2013
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 
Solution

tpa

New Member
Joined
Mar 19, 2013
Messages
6
Ok, how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,140,917
Messages
5,703,169
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top