Replacement Problem

[YasserKhalil]

Hello

I have the following code that I use to replace some text with a nother

Sub Replacement()
    Dim LastRow As Long
    LastRow = Sheets("Conditions").Cells(Rows.Count, "A").End(xlUp).Row
      For x = 1 To LastRow
          Cells.Replace What:=Sheets("Conditions").Range("A" & x), Replacement:=Sheets("Conditions").Range("B" & x), _
          Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _
          False, ReplaceFormat:=False
      Next x
End Sub

If I want to replace for example (John Smith) with (Axel Smith)... There is no problem
But I found that the text (Johny Smith) is replaced with (Axely Smith) .. This is the problem.>>> I want to keep Johny as it is but replace just John

 

[JLGWhiz]

Try changing this:

Code:
Lookat:=xlPart
Code:

To this:

Code:
Lookat:=xlWhole
Code:

 

[YasserKhalil]

Try changing this:

Code:
Lookat:=xlPart
Code:

To this:

Code:
Lookat:=xlWhole
Code:

I tried that but it doesn't work well

 

[Rick Rothstein]

You could use the function I posted in my mini-blog article here...

InStrExact
- Find Location Of A Word, As A Word, Not Embedded Within Another Word

to see if the word you want to replace stands alone as a word (not embedded within other text) and if the function returns a number greater than 0 (indicating the position of the stand-alone word) then perform your replacement on that cell (otherwise skipping it).

 

[YasserKhalil]

You could use the function I posted in my mini-blog article here...

InStrExact
- Find Location Of A Word, As A Word, Not Embedded Within Another Word

to see if the word you want to replace stands alone as a word (not embedded within other text) and if the function returns a number greater than 0 (indicating the position of the stand-alone word) then perform your replacement on that cell (otherwise skipping it).

great function.. But I don't know how to make use of it in the above code
Help me please Mr. Rick Rothstein

Thanks advanced

 

[Rick Rothstein]

great function.. But I don't know how to make use of it in the above code

Add the function from my mini-blog article to your code module and then replace the code you posted with this modification to it...

SSub Replacement()
    Dim LastRow As Long
    LastRow = Sheets("Conditions").Cells(Rows.Count, "A").End(xlUp).Row
      For x = 1 To LastRow
          If InStrExact(1, Sheets("Conditions").Range("B" & x), Sheets("Conditions").Range("A" & x), True) Then
              Cells.Replace What:=Sheets("Conditions").Range("A" & x), _
                            Replacement:=Sheets("Conditions").Range("B" & x), _
                            Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
                            SearchFormat:=False, ReplaceFormat:=False
          End If
      Next x
End Sub

Note: I did not test this (don't have your data setup), but it should work if I have correctly interpretted what you are attempting to do.

 

[YasserKhalil]

Thanks Sir
But It doesn't do what I want to do..
I don't know what's wrong about the code

 

[YasserKhalil]

I have to say that the data in a sheet called "Data" and the conditions in a sheet called "Conditions" which has two columns A and B.

In Data sheet there is one column which I have to run the code on it to be corrected

 

[YasserKhalil]

I'm waiting for your reply Mr. Rick

 

[Rick Rothstein]

I have the following code that I use to replace some text with a nother

Sub Replacement()
    Dim LastRow As Long
    LastRow = Sheets("[B][COLOR=#a52a2a]Conditions[/COLOR][/B]").Cells(Rows.Count, "A").End(xlUp).Row
      For x = 1 To LastRow
          Cells.Replace What:=Sheets("[B][COLOR=#a52a2a]Conditions[/COLOR][/B]").Range("A" & x), Replacement:=Sheets("[B][COLOR=#a52a2a]Conditions[/COLOR][/B]").Range("B" & x), _
          Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _
          False, ReplaceFormat:=False
      Next x
End Sub

If I want to replace for example (John Smith) with (Axel Smith)... There is no problem
But I found that the text (Johny Smith) is replaced with (Axely Smith) .. This is the problem.>>> I want to keep Johny as it is but replace just John

I have to say that the data in a sheet called "Data" and the conditions in a sheet called "Conditions" which has two columns A and B.

In Data sheet there is one column which I have to run the code on it to be corrected

I quoted the code from your original message at the top of this response... note the words I highlighted in red... your replacement code never did anything with a sheet named "Data"... I simply duplicated what you had posted when I wrote my code. If I now understand your setup correctly, I think this code should work for you; but remember, without seeing your setup, I can only guess at what it looks like, so my guess may be wrong, so you may have to adjust the sheet name references so the code points to the correct information on the correct sheets (in case I guessed wrong)...

Sub Replacement()
      Dim LastRow As Long
      LastRow = Sheets("Conditions").Cells(Rows.Count, "A").End(xlUp).Row
        For x = 1 To LastRow
              If InStrExact(1, Sheets("Data").Range("B" & x), Sheets("Conditions").Range("A" & x), True) Then
                    Cells.Replace What:=Sheets("Data").Range("A" & x), _
                                  Replacement:=Sheets("Conditions").Range("B" & x), _
                                  Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
                                  SearchFormat:=False, ReplaceFormat:=False
              End If
        Next x
End Sub