INDEX MATCH of multiple cells in VBA

Question

I want to copy data from 14 columns.

The below formula in Excel works when I drag the formula down and it ignores the blank cells.

=IF(INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="", "", INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))

When I try to replicate this in VBA only the first column is populated.

Range("AU11").Select
ActiveCell.FormulaR1C1 = "=IF(INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="""", """", INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))"
Range("AU11").Select
Selection.AutoFill Destination:=Range("AU11:AU" & lr)

Answer 1

A good practice to get into would be to qualify your workbook and worksheets. This will become useful if you are working with multiple workbooks. Assuming lr has a valid number the below should work.

Option Explicit

    Sub test()
        
        Dim oWB As Workbook: Set oWB = ThisWorkbook
        Dim oWS As Worksheet: Set oWS = oWB.Sheets("Data")  'Change the name of the worksheet to your name
        
        oWS.Range("AU11:AU" & lr).FormulaR1C1 = "=IF(INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="""", """", INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))"
    
    End Sub