![]() 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, _ Sub Sample()ĭim oRange As Range, aCell As Range, bCell As Rangeĭim SearchString As String, FoundAt As String – 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". As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor. It can be used as a VBA function (VBA) in Excel. The UCASE function is a built-in function in Excel that is categorized as a String/Text Function. LetterAfterRoad = Mid(s, InStr(s, road) + Len(road), 1) Description The Microsoft Excel UCASE function converts a string to all upper-case. 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |