CODEWITHSUNDEEP
arraysexcel
Ricardo
Ricardo

Reputation: 51

How to define an array that contains formulas in Excel

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).

Upvotes: 0

Views: 46

Answers (1)

xShen
xShen

Reputation: 572

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

Upvotes: 1

Related Questions