VBA instead of array formulae

tiredofit

Well-known Member
Is it possible to replace array formulae with just VBA?

If I have the data as follows:

Rich (BB code):
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:

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



Alternatively I can wite:

Rich (BB code):
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:

Rich (BB code):
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>
 
Last edited:

mikerickson

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

jmacleary

Well-known Member
Hi there. You could add this code straight after:
Code:
Selection.Value = Selection.Value
Just make sure automatic calculation is on.
 

Some videos you may like

This Week's Hot Topics

Top