VBA sum loops in Formula Arrays

Question

If I have defined named ranges for some blocks - say for example, 2 by 2 blocks named Apple1, Apple2, Apple3, Apple4

and I want to create a Total Block being the sum of the above (in excel, the formula array formula would be {Apple1 + Apple2 + Apple3 + Apple4} With the FormulaArray command - am I on the right lines in my thought process below?

For i = 1 to 2
range("Total").formulaarray = "=" & "Apple" & i
next i

Vasily · Accepted Answer · 2016-06-22 02:53:58Z

1

I've not understand why you need FormulaArray for such task, but you can use this:

Sub test()
    Dim i%, Apples$
    For i = 1 To 4
        Apples = Apples & "+SUM(Apple" & i & ")"
    Next i
    [Total].FormulaArray = "=" & Mid(Apples, 2, Len(Apples))
End Sub

edited Jun 22, 2016 at 2:53

answered Jun 22, 2016 at 2:29

Vasily

5,7923 gold badges22 silver badges36 bronze badges

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

8 Comments

datrubuk Over a year ago

thank you, i will give this a go. Note, the formulaarray is because i want the "Total" to be an 2x2 array as well.

Vasily Over a year ago

@psw very valuable comment, I've understand your point ({=SUM(a+b+c)}), but answer has been posted to show the way of how to put values to array formula, not for give a best possible way to solve the issue of the author. have no idea what behind the OP needs.

PaichengWu Over a year ago

@Vasily, I do not understand your point. Since you use Sum function, why do you use a+b+c? If a+b+c is used, Sum is redundant.

Vasily Over a year ago

@pwc {=SUM(a+b+c)} where a,b,c named ranges, e.g. {=SUM(Apple1+Apple2+Apple3)}

PaichengWu Over a year ago

@Vasily, Why not use {=a+b+c}? It is brevity.

|

PaichengWu · Accepted Answer · 2016-06-22 08:50:05Z

1

After my experiment, I find the behavior of FormulaArray is more complicated.

sub test()
    dim i as long, Apples1 as variant, Apples2 as variant

    for i = 1 to 3
        Apples1 = Apples1 & "Apple" & i & "+"
        Apples2 = Apples2 & "Apple" & i & ","
    next i

    range("total1").FormulaArray = "=sum(" & left(Apples1, len(Apples1)-1) & ")"
    range("total2").FormulaArray = "=sum(" & Apples2 & ")"
    range("total3").FormulaArray = "=" & left(Apples1, len(Apples1)-1)
end sub

the outcome is

If any cells in Apple1, Apple2 or Apple3 are string, the outcome is

edited Jun 22, 2016 at 8:50

answered Jun 22, 2016 at 5:39

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Collectives™ on Stack Overflow

VBA sum loops in Formula Arrays

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related