if cell contains certain value replace that value with another text

[aman2059]

Hello people,

I am trying to complete my work on one macro and is stucked somewhere in between.

My problem is that if a cell contain certain value ( oranges word in big paragraph in the cell in excel), i need to replace it with the value given in the another cell ( in the other cell, the value is apples). My code is given below, i am getting error. Please someone help.

Example if the cell have the below sentence

I have 5 oranges ---> then it should be changed into

I have 5 apples -----> this should be the final sentence

Thanks for help in the advance

Sub finalemailing()
 
 
    Dim clltext1 As String
    Dim clltext As String
    Dim xxwi As String
   
    xxwi = "xxwi"
   
    clltext = ThisWorkbook.Sheets("Email Template").Range("C7").Value
    clltext1 = ThisWorkbook.Sheets("Email Template").Range("C5").Value
           
        If InStr(1, clltext, "xxwi") Then
            clltext.Replace What:="xxwi", Replacement:=clltext1, LookAt:=xlPart, MatchCase:=False
       
        End If
End sub

 

[Trebor76]

Hi aman2059,

I won't try and decipher your code but this is how you would do what you're looking for based on the text in the active cell (adapt to suit):

Option Explicit
Sub Macro1()

    If InStr(ActiveCell, "oranges") > 0 Then
        ActiveCell = Replace(ActiveCell, "oranges", "apples")
    End If

End Sub

Hope that helps,

Robert

 

[aman2059]

Hi Robert,

Thanks for helping me out

I tried the code like below, it didnt work and it didnt give any error too. Not sure what mistake am i making. Could you please check once.

Option Explicit
Sub finalemailing()
 
 Dim clltext As String
 Dim clltext1 As String
 Dim body As String
   
    clltext = ThisWorkbook.Sheets("Email Template").Range("C7").Value
    clltext1 = ThisWorkbook.Sheets("Email Template").Range("C5").Value
   
    If InStr(clltext, "xxwi") > 0 Then
   
    clltext = Replace(clltext, "xxwi", "clltext1")
   
    End If

End sub

 

[aman2059]

the 'xxwi' should be replaced with the whole value in cell C5

 

[Trebor76]

I can't really work out what your code is doing but the clltext1 in this line...

clltext = Replace(clltext, "xxwi", [B]"[/B]clltext1[B]"[/B])

...is actually just a string. Remove the quotes (which I've bolded) if you want to return the string variable value i.e. what's in cell C5 of the Email Template tab.

The replace function does just that - replaces text with something else. Your code will replace just "xxwi" with what's in cell C5 of the Email Template tab once you remove the quotes. If you want clltext1 if "xxwi" is in cltext then just do this:

Option Explicit
Sub finalemailing()
 
 Dim clltext As String
 Dim clltext1 As String
 Dim body As String
   
    clltext = ThisWorkbook.Sheets("Email Template").Range("C7").Value
    clltext1 = ThisWorkbook.Sheets("Email Template").Range("C5").Value
   
    If InStr(clltext, "xxwi") > 0 Then
        clltext = clltext1
    End If

End Sub

Regards,

Robert