![]() If aCell.Address = bCell.Address Then Exit DoĪCell.Value = repl(aCell. Set aCell = oRange.FindNext(After:=aCell) LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ĪCell.Value = repl(aCell.Value, SearchString) ![]() Set aCell = oRange.Find(What:=SearchString & "?", LookIn:=xlValues, _ Click on the Before Change button on the Table ribbon: Step 2: Select SetField from the drop down list: Step 3: After selecting SetFields, 2 fields will be added: Name: This is the name of the field that will be modified. Sub Sample()ĭim oRange As Range, aCell As Range, bCell As Rangeĭim SearchString As String, FoundAt As String Contents Step 1: Select and open the table you want to apply the macro to from the Navigation Pane on the left. – Jean-François Corbett 1 hour agoĪlso checking for the scenario where the cell can contain multiple "road" values (as shown in snapshot below which has a 'before' and 'after' snapshot. That's the (mildly) interesting twist of this problem. You have to figure out what ? is and make it uppercase. OP is not only looking for roadh but for any road? where ? is a letter a-z. xlPart in " LookAt:=xlPart" takes care of that :) You don't need to use the wildcard character to check for the presence of a string inside a string. Range("A1:A1000").Replace What:=SearchString, Replacement:=ReplaceString, _ '~~> Replace the range below with the respective range If the code finds any word then it will automatically replace. You don't need to find the word if your final intention is to replace the word. FIND/.FINDNEXT ?įIND/FINDNEXT is far much more faster then looping and searching for values in Excel Cells )ĪND the below is even faster ( in fact the fastest). LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _Īlso instead of looping you might want to use. Range("A1").Replace What:=SearchString, Replacement:=ReplaceString, _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ Set aCell = Range("A1").Find(What:=SearchString, LookIn:=xlFormulas, LookAt _ Let Excel do the dirty work ) Sub Sample() Mid(s, InStr(s, road)) = UCase(road & charAfterRoad)ĭebug.Print s ' returns "play that ROADHouse blues" ![]() 'Found "road" and there is at least one char after it.ĬharAfterRoad = Mid(s, roadPos + Len(road), 1) If roadPos > 0 And Len(s) >= roadPos + Len(road) Then Here is how I would do it: Const road As String = "road" If what you're looking for is road? where ? is a letter a-z then let Like look for a-z rather than manually typing the entire alphabet. If I were you, I would heed sarcastic remark. Mid(s, InStr(s, road)) = UCase(road & letterAfterRoad)ĭebug.Print s ' returns "play that ROADHouse blues". LetterAfterRoad = Mid(s, InStr(s, road) + Len(road), 1) ![]() S = "play that roadhouse blues" ' or get contents of some cell This will do the trick: Const road As String = "road" ![]()
0 Comments
Leave a Reply. |