After extensive research I finally found a VBS code to find and replace words in paragraphs only if its an exact match. The only problem I have is that the code only replaces a certain word only once in a specific cell.
So columns A through C have a bunch of paragraphs of text. Using this macro, it automatically scans all the words in column D and if it finds those words anywhere in columns A through C, it will replace it with the word next to it on column E. So if the sentence is "Hello how is hell hell?" in column A. And in column D there is a word hell - then the code below doesn't replace "hello" at all (working as intended), it does replace the first "hell" in the sentence (working as intended), but does not replace the second "hell" (not working as intended).
Anyone have any idea how to tweak the code so all words get replaced, and not just the first instance?
The code:
Code above found from second to last post in: http://www.mrexcel.com/forum/excel-questions/468331-find-replace-exact-only-2.html
So columns A through C have a bunch of paragraphs of text. Using this macro, it automatically scans all the words in column D and if it finds those words anywhere in columns A through C, it will replace it with the word next to it on column E. So if the sentence is "Hello how is hell hell?" in column A. And in column D there is a word hell - then the code below doesn't replace "hello" at all (working as intended), it does replace the first "hell" in the sentence (working as intended), but does not replace the second "hell" (not working as intended).
Anyone have any idea how to tweak the code so all words get replaced, and not just the first instance?
The code:
Code:
Sub exa()Dim _
rngLookFor As Range, _
rngDataCell As Range, _
strFormula As String
For Each rngLookFor In Range("D1:D99")
For Each rngDataCell In Range("A1:C99")
strFormula = rngDataCell.Text
If ReturnReplacement(rngLookFor.Text, rngLookFor.Offset(, 1).Text, strFormula) Then
rngDataCell.Value = strFormula
End If
Next
Next
End Sub
Function ReturnReplacement(LookFor As String, _
ReplaceWith As String, _
FormulaString As String _
) As Boolean
Static REX As Object '<--- RegExp
If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
REX.Global = False
REX.IgnoreCase = True
End If
With REX
.Pattern = "\b" & LookFor & "\b"
If .Test(FormulaString) Then
FormulaString = .replace(FormulaString, ReplaceWith)
ReturnReplacement = True
End If
End With
End Function
Code above found from second to last post in: http://www.mrexcel.com/forum/excel-questions/468331-find-replace-exact-only-2.html
Last edited: