Index Match VBA XL Down

[jamesmev]

I am using the below VBA

Sub IMBRAND()
    Dim J_J As Long

J_J = Range("A" & Rows.Count).End(xlUp).Row
    Range("J2").Formula = "=IFERROR(INDEX(MatVariable!C[-6],MATCH([@PS],MatVariable!C[-8],FALSE)),"""")"
    Range("J2").AutoFill Destination:=Range("J2:J" & J_J), Type:=xlFillSeries
    ActiveSheet.Calculate
    Columns("J:J").Copy
    Columns("J:J").PasteSpecial Paste:=xlPasteValues

End Sub

To do an Index Match on Column J to a table within MatVariable (Column C).
When executing the code I am getting a run time 1004 error. Unsure where the break is.

 

[Fluff]

You need to use Formula R1C1 rather than Formula