VBA instead of array formulae

[tiredofit]

Is it possible to replace array formulae with just VBA?

If I have the data as follows:

1    10
2    25
3    30

and I want to sum column A and put the result in cell D1, I can record a macro to generate this code:

Range("D1").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:R[2]C[-3])"

Alternatively I can wite:

Range("D1").Value = Range("A1").Value+Range("A2").Value+Range("A3").Value
​​

If on the other hand I want to use the LINEST function, the recorded macro shows:

Range("F1:G1").Select
    Selection.FormulaArray = "=LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5])"

How can I rewrite this so the formula is NOT shown in Excel?

Thanks

_{^{<strike>
</strike>}}​

​

_{^{<strike>
</strike>}}

 

[mikerickson]

Try

Range("F1:G1").Select
With Selection
    .FormulaArray = "=LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5])"
    .Value = .Value
End With

 

[FormR]

Hi, maybe:

Range("F1:G1").Value = Evaluate("LINEST(B1:B3,A1:A3)")

 

[jmacleary]

Hi there. You could add this code straight after:

Selection.Value = Selection.Value

Just make sure automatic calculation is on.

 

[tiredofit]

Thanks for the suggestions.