Formula (in VBA) to use both relative positioning from active cell and static positioning (From cell A1)

[nshepo20]

I have a lookup formula that I need to be compatible in VBA. The struggle I am having is that all sheets I loop through are the same number of columns until past column S where the number of columns may change. Thus I need the formula to reference column S as my lookup variable and output a cost figure contained in the second to last 3 columns of data based on the second to last 3 headers and then fill down the rows. The headers need to be absolute reference and the S column needs to be relative. Please see code and notes in code below... Also attached is a visual of what I am trying to do... Is this possible???

Thanks!

Sub Macro3()
'
' Macro3 Macro
'

'
Dim r As Integer
r = (Cells(Rows.Count, "R").End(xlUp).Row) - 16

    Range("B16").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell = "COST"
    ActiveCell.Offset(1, 0).Range("A1").Select
   
    'R1C1 Format (R17C19) needs to reference cell S17 but needs to be relative reference not absolute. Adding brackets causes excel to use the active cell (AF17) as the reference point as opposed to A1.
    'On the otherhand, R[-1]C[-3] ...etc needs to be an absolute reference but I get a syntax error when removing the brackets.
    'R[-1]C[-4]:R[-1]C[-2] also needs to be an absolute reference.
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(IF(ISNUMBER(SEARCH(R17C19,R[-1]C[-3]))=TRUE,IF(ISNUMBER(FIND(""COST"",R[-1]C[-3],1))=TRUE,R[-1]C[-3],0),IF(ISNUMBER(SEARCH(S17,R[-1]C[-2]))=TRUE,IF(ISNUMBER(FIND(""COST"",R[-1]C[-2],1))=TRUE,R[-1]C[-2],0),IF(ISNUMBER(SEARCH(S17,R[-1]C[-4]))=TRUE,IF(ISNUMBER(FIND(""COST"",R[-1]C[-4],1))=TRUE,R[-1]C[-4],0)))),R[-1]C[-4]:R[-1]C[-2],R[0]C[-4]:R[0]C[-2],0,0)"
  
    'Original A1 Formatted Formula
    'ActiveCell.Formula = "=XLOOKUP(IF(ISNUMBER(SEARCH(S17,$Z$16))=TRUE,IF(ISNUMBER(FIND(""COST"",$Z$16,1))=TRUE,$Z$16,0),IF(ISNUMBER(SEARCH(S17,$AA$16))=TRUE,IF(ISNUMBER(FIND(""COST"",$AA$16,1))=TRUE,$AA$16,0),IF(ISNUMBER(SEARCH(S17,$Y$16))=TRUE,IF(ISNUMBER(FIND(""COST"",$Y$16,1))=TRUE,$Y$16,0)))),$Y$16:$AA$16,Y17:AA17,0,0)"
  
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & r)
End Sub

 

[nshepo20]

Figured it out never mind!

Solution:

ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(IF(ISNUMBER(SEARCH(R[0]C19,R16C[-3]))=TRUE,IF(ISNUMBER(FIND(""COST"",R16C[-3],1))=TRUE,R16C[-3],0),IF(ISNUMBER(SEARCH(R[0]C19,R16C[-2]))=TRUE,IF(ISNUMBER(FIND(""COST"",R16C[-2],1))=TRUE,R16C[-2],0),IF(ISNUMBER(SEARCH(R[0]C19,R16C[-4]))=TRUE,IF(ISNUMBER(FIND(""COST"",R16C[-4],1))=TRUE,R16C[-4],0)))),R16C[-4]:R16C[-2],RC[-4]:RC[-2],0,0)"