R1C1 notation not working in VBA formula

[canyon]

I have the following formula I copied from using the macro recorder. The issue is that it seems to remain an absolute value instead of relative. Where RC[-5] should be D2 and then the next cell D3, D4... etc. When I substitute the R[-5] for D2 the formula doesn't work.

Is this because its an array formula?

For Each cell In RNG

range(Cells(2, 9), Cells(LastRow, 9)).FormulaArray = _
        "=INDEX('[filename.xlsx]sheetname'!C2,MATCH(""3730""&RC[-5],'[filename.xlsx]sheetname'!C1&'[filename.xlsx]sheetname'!C5,0))"


    
    
    
Next cell

End Sub

 

[RoryA]

You need to enter the formula into one cell, then fill it down.

 

[canyon]

You need to enter the formula into one cell, then fill it down.

You need to enter the formula into one cell, then fill it down.

Interesting. Array formulas are new to me so I was not aware.

Dim LastRow As Long

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

range(Cells(2, 9), Cells(LastRow, 9)).FormulaArray = _
        "=INDEX('[filename.xlsx]sheetname'!C2,MATCH(""3730""&RC[-5],'[filename.xlsx]sheetname'!C1&'[filename.xlsx]sheetname'!C5,0))"
        
range("I2").AutoFill Destination:=range(Cells(2, 9), Cells(LastRow, 9)), Type:=xlFillDefault

I've included my autofill code but it seems to still do the same thing.

 

[RoryA]

You're still populating the entire range with the formula initially. This part:

range(Cells(2, 9), Cells(LastRow, 9)).FormulaArray

should be:

Cells(2, 9).FormulaArray

 

[canyon]

You're still populating the entire range with the formula initially. This part:

range(Cells(2, 9), Cells(LastRow, 9)).FormulaArray

should be:

Cells(2, 9).FormulaArray

Thank you very much!

 

[canyon]

You're still populating the entire range with the formula initially. This part:

range(Cells(2, 9), Cells(LastRow, 9)).FormulaArray

should be:

Cells(2, 9).FormulaArray

Hi,

I keep getting this error and am unsure what its impacting:

"Unable to set the FormulaArray property of the Range class"

Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row

Cells(2, 9).FormulaArray = _
        "=(INDEX('[filename.xlsx]sheet'!$B:$B,MATCH(""3730""&RC[-5],'[filename.xlsx]sheet'!$A:$A&'[filename.xlsx]sheet'!$E:$E,0))"
range("I2").AutoFill Destination:=range(Cells(2, 9), Cells(LastRow, 9)), Type:=xlFillDefault

 

[RoryA]

How long is the actual formula you are using? FormulaArray is limited to 255 characters.

 

[canyon]

How long is the actual formula you are using? FormulaArray is limited to 255 characters.

That could do it, I replaced the actual workbook and sheets on here with place holders. I've done the same in my VBA code now, getting 194 characters...

Cells(2, 9).FormulaArray = "=INDEX('[" & filepath & "]" & sheetname & "'!$B:$B,MATCH(""3730""&RC[-5],'[" & filepath & "]" & sheetname & "'!$A:$A&'[" & filepath & "]" & sheetname & "'!$E:$E,0))"