In cell AU2 I have the following formula I want to use in a macro. I want to be able to hard code the number of rows being using (relative value)
=IF(H2<>"","",IFERROR(INDEX($I$2:$I$23,MATCH(VLOOKUP(AP2,$AP$2:$AT$23,5,0),$AT$2:$AT$23,0)),""))
With the macro recorder I get the following formula:
Sub EXAMPLE()

Dim LastRow As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AgentRow = 21
    Range("AU2:AU" & LastRow).FormulaR1C1 = "=IF(RC[-39]<>"""","""",IFERROR(INDEX(R2C9:R[21]C9,MATCH(VLOOKUP(RC[-5],R2C42:R[21]C46,5,0),R2C46:R[21]C46,0)),""""))"
    Range("AU2:AU" & LastRow) = Range("AU2:AU" & LastRow).Value
    
End Sub
I want to hard code the "R[21]" portion of formula. I tried changing code to following but it is not working:
Sub EXAMPLE()

Dim LastRow As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AgentRow = 21
    Range("AU2:AU" & LastRow).FormulaR1C1 = "=IF(RC[-39]<>"""","""",IFERROR(INDEX(R2C9:R["AgentRow"]C9,MATCH(VLOOKUP(RC[-5],R2C42:R["AgentRow"]C46,5,0),R2C46:R["AgentRow"]C46,0)),""""))"
    Range("AU2:AU" & LastRow) = Range("AU2:AU" & LastRow).Value
    
End Sub