Replace specific text with value from another cell

[capo4410]

Hello all,

I’m trying to create a macro, but am having some difficulties. I am looking to search a cell (“K2”) for the word Location. Once found I would like to replace that word with the contents located in cell “L2”. This sheet will have many rows and the information within column L will always different. I thought a find replace code would be sufficient, but couldn’t it to work when taking data from a different cell.

Any help would be greatly appreciated. Thank you!

 

[DanteAmor]

Try this

Sub Replace_specific_text()
    Dim c As Range
    For Each c In Range("K1", Range("K" & Rows.Count).End(xlUp))
        n = InStr(1, LCase(c.Value), LCase("Location"))
        If n > 0 Then c.Value = Left(c.Value, n - 1) & c.Offset(0, 1).Value & Mid(c.Value, n + 8)
    Next
End Sub

 

[capo4410]

Worked perfectly, Dante! Thank you very much!

 

[DanteAmor]

Worked perfectly, Dante! Thank you very much!

I'm glad to help you. Thanks for the feedback.