Error with LINEST formula in VBA

Question

I get this error:

unable to set the formulaarray property of the range class

Code

Dim i As Integer

For i = 0 To 10

    Range("AE3:AE5").Select
    Selection.FormulaArray = _
        "=LINEST(R[0+i]C[-12]:R[51+i]C[-12],R[0+i]C[-6]:R[51+i]C[-4],TRUE,TRUE)"
    Range("AE5").Select
    Selection.Copy

    Cells(3 + i, 29).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next i
End Sub

I have read online that it is due to character length being >255. However I don't quite see how my the FormulaArray above has more than 255 characters.

Would really really appreciate any advice on this! (: Thank you.

You must place the is outside the quotes as otherwise they will appear as literal strings and Excel doesn't know what i represents. — SJR

SJR SJR · Accepted Answer · 2017-09-04T13:03:22

I think this should do it

Sub x()

Dim i As Integer

For i = 0 To 10
    Range("AE3:AE5").FormulaArray = _
        "=LINEST(R[" & i & "]C[-12]:R[" & 51 + i & "]C[-12],R[" & i & "]C[-6]:R[" & 51 + i & "]C[-4],TRUE,TRUE)"
    Cells(3 + i, 29).Value = Range("AE5").Value
Next i

End Sub

Error with LINEST formula in VBA

1 Answers