Variable number of terms in Excel VBA Formula?

Question

Is it possible to write a formula in VBA for excel, so that the formula has "n" members, and the number changes as the value of "n"?

For example, let's say you wanted to encode cell a1 so that it was the sum of a2 and a3. Then you need b1 to be the sum of b2, b3, b4, b5, etc., so that each cell in column 1 row 1 for a range of cells is the sum of “n” cells below it, where “n” changes from column to speaker. Let's say that all the addresses of the cells that you want to use are known and stored in an array.

Here is some code to better explain what I'm asking:

For i = 0 to n
   Range(arr1(i)).formula = "=" & range(arr2(i)).value & "-(" _
   & Range(arrk(i)).value & "+" & Range(arrk+1(i)).value & "+" _
   & Range(arrk+2(i)).value & "+" & ... & ")"
Next i

So, I'm looking for one piece of VBA code that can make a cell formula contain a dynamic number of terms. In the above code, the value of the cell is a1 = a- (b + c + d + ...), where the number of terms in the bracket is a variable, depending on which cell the formula applies to.

The image here shows an example of what I want to do. I need a code that can take "summer income" and subtract from it a variable amount of "expenses", where the number of expenses changes every year (but the number remains fixed for this year). The code must use a formula so that the user can make changes to the cost entries.

Answer 1

, ( , ) .

, "" Junk, , .

, for application.index. Join, , . @thepiyush13, array.formula .

, , :

' hypothetical array containing two sets of cells to use
Dim array1(0 To 1, 0 To 1) As Variant
Dim vartemp As Variant
Dim vartemptransposed As Variant

' col 1 will be used to add I10 and I13, col2 I11 and I14
array1(0, 0) = "$I$10"
array1(1, 0) = "$I$13"
array1(0, 1) = "$I$11"
array1(1, 1) = "$I$14"

For i = 1 to 2

'application.index(arr,row#,col#) to create a new array 
vartemp = Application.Index(array1, 0, i)
'error if not transposed
vartemptransposed = Application.Transpose(vartemp)

randomstring = Join(vartemptransposed, ",")
totalvalue = 100

'example formula: a1 = totalvalue - sum(I10,I13). a2 = totalvalue - sum(I11,I14)
    Cells(1,i).formula = "=" & totalvalue & "-SUM(" & randomstring & ")"
Next i

, , , " ". , , .

Answer 2

Array Formula?

: Excel ( " " ) .

: http://www.excelfunctions.net/Excel-Array-Formulas.html