Highlight Range Col B - Replace String 'Test' VBA

[MikeL]

Selected range is in Col B..

1) Find all instances of the 'string 'Test'
2) replace the string 'Test" with the value in Col C, same row for each instance found


Thanks

 

[Peter_SSs]

Is this a case-sensitive search? That is, should the string 'test' also be replaced with the adjacent value?

Will the string 'Test' ever occur more than once in a single cell?

Can you confirm that you are looking for the string 'Test' as you stated not the word 'Test'? That is the red text here would get replaced "Testing proved easy"

 

[MikeL]

Is this a case-sensitive search? That is, should the string 'test' also be replaced with the adjacent value?

Will the string 'Test' ever occur more than once in a single cell?

Can you confirm that you are looking for the string 'Test' as you stated not the word 'Test'? That is the red text here would get replaced "Testing proved easy"

Hi, not case sensitive, only 1x per cell, and it is a string

 

[Peter_SSs]

Try this with a copy of your data.

Sub Replace_String()
  Dim a As Variant
  Dim i As Long
  
  With Selection
    a = .Resize(, 2).Value
    For i = 1 To UBound(a)
      a(i, 1) = Replace(a(i, 1), "test", a(i, 2), 1, -1, 1)
    Next i
    .Value = a
  End With
End Sub