How to define an array that contains formulas in Excel

Question

ideally I would like a solution that does not imply using VBA, but if that is the only way you know go ahead.

Does it exist any way of creating or emulating an array in excel that contains formulas instead of being static?

I know you can create a 1x2 array of constant values with ={10,20}. But what I need instead is to be able to create a 1x2 array of formulas something like ={10*A1,20*A2}. Actually the formula that I need to use in the array isn't a multiplication but a =Forecast.Linear(x,Ys,Xs).

It needs to be a 1x2 array because this part will be a parameter of another larger formula (another forecast.linear).

Not clear what you are trying to do, but it is easy enough to create an array of strings in VBA. — John Coleman
– John Coleman, Commented Aug 15, 2021 at 23:06

xShen · Accepted Answer · 2021-08-16 05:14:18Z

1

It is not clear what you are trying to achieve by filling an array with formula. but this is one way to do it.

Sub getArray()
Dim Arr As Variant, X As Long, I As Long
Arr = Range("B1:C19").Formula
' and this is how to extract this array
 For I = LBound(Arr, 2) To UBound(Arr, 2)
    For X = LBound(Arr, 1) To UBound(Arr, 1)
    Debug.Print Arr(X, I)
   Next X
  Next I
End Sub

answered Aug 16, 2021 at 5:14

xShen

5821 gold badge4 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to define an array that contains formulas in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related