Replace Text in a Range of Cells

[gberg]

I am looking for a formula to replace text in a range of cells. The information will always start in Cell "I14" but will very in how long the list is. I have come up with a way to add a XLOOKUP formula into the affected cells but it uses "ActiveCell" which is always I14 so everything is based on what is in I14. Is there a way to have the date from individual cells (i.e. I14, I15, I16, etc.) in the following?

Sub Replace()

    Range("I14").Select
    Range(Selection, Selection.End(xlDown)).Select

    Dim myRange As Range
    Set myRange = Selection
    myRange.Formula = _
        "=XlOOKUP(" & """" & ActiveCell & """" & ",TBL_Status[STATUS],TBL_Status[Replacement],"""",0)"


End Sub

Thanks

 

[Zot]

To write a formula to a range, you can just write the formula for 1st row and the subsequent row will change automatically. Example:

Sub Replace()

Range("A1", "A10").Formula = "= B1"

End Sub

You can see the formula in A1 to A10 as
A1 > =B1
A2 > =B2
A3 > =B3

and so on.