Replacing text in a cell using VBA macro

[FvM1985]

Hi,

I have the following problem:

I am using a bigger VBA macro, as a part of this I want to search for a certain code(s) in a cell with multiple codes, and convert the entire cell with a new code/text. For example if a cell contains "NDM TRL WE5 YIC ZAH", I want to search for "TRL" and replace the entire cell with "Trend". And I have 6 other codes like "TRL" which will have to be replaced by other codes/texts..

I hope someone can help me.

This is what I have so far:
Range(Line).Select
ActiveCell.Formula = "=IF(ISNUMBER(SEARCH(""TRL"",$CF1,1)),""Trend"","
Range(Line).Select
Selection.Copy
Range(Range_Line).Select
ActiveSheet.Paste

 

[FvM1985]

After the text replacement, the macro has to continue with the following:

Range(BTO_VUV).Select
ActiveCell.Formula = "=IF(AND($AD1<>""Backorder CB"",$AD1<>""Backorder""),"""",IF(AND(OR($AD1=""Backorder CB"",$AD1=""Backorder""),(($L1-$AB1)<8)),""BTO"",""VUV""))"
Range(BTO_VUV).Select
Selection.Copy
Range(Range_BTO_VUV).Select
ActiveSheet.Paste

Etc.

I'm looking for a way to stop the part with the text replacement, after replacing the 15 codes.

 

[Fluff]

Assuming that BTO_VUV & Range_BTO_VUV are both named ranges try

Sub ReplaceText()

   Dim ary As Variant
   Dim Cnt As Long
   
   ary = Split("*TRL*|Trend|*ABC*|Start|*XYZ*|End", "|")
   With Sheets("Check")
      With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         For Cnt = 0 To UBound(ary) Step 2
            .Replace ary(Cnt), ary(Cnt + 1), xlPart, , False, , False
         Next Cnt
      End With
   End With
   .Range("BTO_VUV").Formula = "=IF(AND($AD1<>""Backorder CB"",$AD1<>""Backorder""),"""",IF(AND(OR($AD1=""Backorder CB"",$AD1=""Backorder""),(($L1-$AB1)<8)),""BTO"",""VUV""))"
   .Range("BTO_VUV").Copy Range("Range_BTO_VUV")
End Sub

But I don't understand what you mean by

I'm looking for a way to stop the part with the text replacement, after replacing the 15 codes.

 

[FvM1985]

After replacing the 15 codes in total, the macro keeps searching for another code and then I get the "subscript out of range" error.

Assuming that BTO_VUV & Range_BTO_VUV are both named ranges try

Sub ReplaceText()

   Dim ary As Variant
   Dim Cnt As Long
   
   ary = Split("*TRL*|Trend|*ABC*|Start|*XYZ*|End", "|")
   With Sheets("Check")
      With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         For Cnt = 0 To UBound(ary) Step 2
            .Replace ary(Cnt), ary(Cnt + 1), xlPart, , False, , False
         Next Cnt
      End With
   End With
   .Range("BTO_VUV").Formula = "=IF(AND($AD1<>""Backorder CB"",$AD1<>""Backorder""),"""",IF(AND(OR($AD1=""Backorder CB"",$AD1=""Backorder""),(($L1-$AB1)<8)),""BTO"",""VUV""))"
   .Range("BTO_VUV").Copy Range("Range_BTO_VUV")
End Sub

But I don't understand what you mean by

 

[Fluff]

Could you please post your complete code, using code tags (the # icon in the reply window)

 

[FvM1985]

I have made it work, thanks for all your help!

Could you please post your complete code, using code tags (the # icon in the reply window)

 

[Fluff]

Glad you got it sorted & thanks for the feedback