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 `i`s outside the quotes as otherwise they will appear as literal strings and Excel doesn't know what i represents. — SJR, Sep 04 '17 at 12:57
Also try avoiding `.Select`, see [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more. — Plagon, Sep 04 '17 at 12:58

score 1 · Accepted Answer · answered Sep 04 '17 at 13:03

1

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

answered Sep 04 '17 at 13:03

SJR

22,986
6
18
26

1

Thank you very much indeed, all those comments and the solution were extremely for me to learn! (: – Wei Liang Wee Sep 05 '17 at 03:26

Error with LINEST formula in VBA

1 Answers1