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

 

[steve the fish]

Hi. Heres one way. Not particularly quick but easy to understand. Just change the arrays, the sheet name and range to suit:

Application.ScreenUpdating = False

arr1 = Array("TRL", "DEF", "GHI")
arr2 = Array("Trend", "2nd", "3rd")

For Each c In Sheets("Sheet1").Range("A1:A5")
    For i = LBound(arr1) To UBound(arr1)
        If InStr(UCase(c), UCase(arr1(i))) > 0 Then
            c.Value = arr2(i)
        End If
    Next
Next

Application.ScreenUpdating = True

 

[frank_AL]

You can use the VBA function "InStr" to determine if the text string exists:
Repeat that line of code for the other values you want to test for and replace

If InStr(line, "TRL") > 0 Then line.Value = "Trend"

 

[FvM1985]

Thanks! It is working, but indeed quite slow. The entire macro just took 25min to run..

Anyone with a quicker solution?

Hi. Heres one way. Not particularly quick but easy to understand. Just change the arrays, the sheet name and range to suit:

Application.ScreenUpdating = False

arr1 = Array("TRL", "DEF", "GHI")
arr2 = Array("Trend", "2nd", "3rd")

For Each c In Sheets("Sheet1").Range("A1:A5")
    For i = LBound(arr1) To UBound(arr1)
        If InStr(UCase(c), UCase(arr1(i))) > 0 Then
            c.Value = arr2(i)
        End If
    Next
Next

Application.ScreenUpdating = True

 

[FvM1985]

Thanks, can I also use this function in a single range, like "A1:A5" for example?

You can use the VBA function "InStr" to determine if the text string exists:
Repeat that line of code for the other values you want to test for and replace

If InStr(line, "TRL") > 0 Then line.Value = "Trend"

 

[Fluff]

Another option

Sub ReplaceText()

   Dim Ary As Variant
   Dim Cnt As Long
   
   Ary = Split("*[COLOR=#ff0000]TRL[/COLOR]*|[COLOR=#ff0000]Trend[/COLOR]|*[COLOR=#ff0000]ABC[/COLOR]*|[COLOR=#ff0000]Start[/COLOR]|*[COLOR=#ff0000]XYZ[/COLOR]*|[COLOR=#ff0000]End[/COLOR]", "|")
   With Sheets("[COLOR=#0000ff]Check[/COLOR]")
      With .Range("[COLOR=#0000ff]A2[/COLOR]", .Range("[COLOR=#0000ff]A[/COLOR]" & 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
   
End Sub

Change values in red to match your search/replace items. And change sheet name & range in blue to match

 

[frank_AL]

Another option

Sub ReplaceText()

   Dim Ary As Variant
   Dim Cnt As Long
   
   Ary = Split("*[COLOR=#ff0000]TRL[/COLOR]*|[COLOR=#ff0000]Trend[/COLOR]|*[COLOR=#ff0000]ABC[/COLOR]*|[COLOR=#ff0000]Start[/COLOR]|*[COLOR=#ff0000]XYZ[/COLOR]*|[COLOR=#ff0000]End[/COLOR]", "|")
   With Sheets("[COLOR=#0000ff]Check[/COLOR]")
      With .Range("[COLOR=#0000ff]A2[/COLOR]", .Range("[COLOR=#0000ff]A[/COLOR]" & 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
   
End Sub

Change values in red to match your search/replace items. And change sheet name & range in blue to match

Fluff, thanks for sharing how to provide a solution for the OP with an approach using Arrays. This is why I continue to participate on this Forum. I learn something everyday that I can apply going forward!

 

[Fluff]

Fluff, thanks for sharing how to provide a solution for the OP with an approach using Arrays. This is why I continue to participate on this Forum. I learn something everyday that I can apply going forward!

Thanks for that, it's also why I come here & how I've learnt so much

 

[FvM1985]

Thank you for your help so far Fluff, it is working really good (and fast).

I still have one problem; after replacing the text the macro needs to proceed with the remaining part of my entire macro but i am receiving an error. Error 9 to be precise, subscript out of range..

Any quick solutions for this error?

Thank you very much.

Another option

Sub ReplaceText()

   Dim Ary As Variant
   Dim Cnt As Long
   
   Ary = Split("*[COLOR=#ff0000]TRL[/COLOR]*|[COLOR=#ff0000]Trend[/COLOR]|*[COLOR=#ff0000]ABC[/COLOR]*|[COLOR=#ff0000]Start[/COLOR]|*[COLOR=#ff0000]XYZ[/COLOR]*|[COLOR=#ff0000]End[/COLOR]", "|")
   With Sheets("[COLOR=#0000ff]Check[/COLOR]")
      With .Range("[COLOR=#0000ff]A2[/COLOR]", .Range("[COLOR=#0000ff]A[/COLOR]" & 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
   
End Sub

Change values in red to match your search/replace items. And change sheet name & range in blue to match

 

[Fluff]

What is the rest of your macro?