Correlation Matrix through relative refrances

[hockeyfan]

I am trying to find correllations between an fixed array and then four others through relative refrancing.


Sub x()

Dim i As Integer
i = 0

For i = 0 To 5


Cells(9, 2 + i).FormulaR1C1 = "=CORREL(R[-7]C:R[-3]C,R[-7]C[1 + i]:R[-3]C[1+i])"

Next

End Sub



I thought this would work, but I get a application or object defined error

[mikerickson]

The i in the formula is causing a #NAME! error.

[VBA]Cells(9, 2 + i).FormulaR1C1 = "=CORREL(R[-7]C:R[-3]C,R[-7]C[" & (1 + i) & "]:R[-3]C[" & (1 + i) & "])"[/VBA]