CODEWITHSUNDEEP
excelvba
datrubuk
datrubuk

Reputation: 31

VBA sum loops in Formula Arrays

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

Upvotes: 1

Views: 507

Answers (2)

PaichengWu
PaichengWu

Reputation: 2689

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 enter image description here

If any cells in Apple1, Apple2 or Apple3 are string, the outcome is enter image description here

Upvotes: 1

Vasily
Vasily

Reputation: 5782

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

enter image description here

Upvotes: 1

Related Questions