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?
Thanks
VBA Code:
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