Dynamic range in a loop

Hi,
Im trying to insert formulas into adjacent cell of a reference cell in a column . The formula needs dynamic range. IS there a way we can do this with VBA?

Sub Macro2()
'
' Macro2 Macro

Dim LR As Long
Dim i As Long
Dim text As String




Sheets("Sheet1").Select
LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
  
    
    For i = 1 To LR
    If Cells(i, 4).Value2 = "text1" Then
           
    Range("E" & i).Select

        Activecell.FormulaR1C1 = "=LOOKUP(2,1/(R1C1:RC1<>""),(R1C1:RC1)"
         ' For example im trying to insert e5=LOOKUP(2,1/($a$1:$a5<>""),($a$1:$a5))
     End If
 Next
 End Sub

Capture11.PNG

This should do it. Just change "d" to whatever you were looking for.

Untitled.png