Array Formula (sum of ranges)

[kunguito]

Is there any way to pass ranges to a SUM formula instead of using "A1:A15" notation?

I tried that but it's not working.


[vba]
Dim Rg As Excel.Range
Dim RgArray(8) As Excel.Range
Rg.FormulaArray = "=SUM(RgArray(0),RgArray(1),RgArray(2),RgArray(3),RgArray(4),RgArray(5),RgA rray(6),RgArray(7),RgArray(8))"[/vba]

[Bob Phillips]

What exactly are you trying to do, in English sentences, that is very confusing code.

[kunguito]

What I asked in the previous post:
Is it possible to insert a function that refers to ranges as objects instead of string indexes like "A1:A5"?


What i'm actually looking for:
I just want to insert a function in a range that performs a Vector type sum (without any loop) out of a variable number of ranges of the same dimension.

[Bob Phillips]

Well that clears it up then (or not as the case may be).

Ranges or arrays? Same dimension - meaning what? And so on.

[kunguito]

I'm working with Ranges (object Excel.Range) , but just one dimension (like a vector)

Same dimension meaning that ranges have the same amount of cells (same length).



Thanks xld!


God! It's sometimes hard to make oneself understood!

[Bob Phillips]

You're right it is, but you are failing (at least with me).

You are not working with ranges, just declaring an array of type range doesn't get anything in there. And if you want to sum them, I preume that you want to just sum the values, so what is wrong with

[vba]

Range("H1").Formula = "=SUM(A1:A10)"
[/vba]

but you say that you don't want to use A1:A10 notation, but don't say why not. If it is because the range is determined dynamically within the code you can use

[vba]

Range("H1").Formula = "=SUM(" & some_range.Address & ")"
[/vba]

If not, then I still am at a loss as to what you are trying to do.

[kunguito]

Hi xld,

I hope you didn't missinterpret me. My mother tongue isn't English. I didn't mean to call you stupid or anything like that.

I am working with an array of Ranges. It's just I didn't show the code where their content is loaded.

Your tip answers the first q?estion. It's actually like building an SQL statement on a string. Should have thought about that before.

But the second issue is still unanswered. I'll try again.
I want column A to be the sum of column B and C.
And I want that to be dynamic (with a formula), and I'd rather not to have to run a loop (lazy, yes but who isn't).

[mikerickson]

[VBA]With ThisWorkbook.Sheets("Sheet1").Range("A:A")
With Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlUp))
.FormulaR1C1 = "RC[1] + RC[2]"
.Value = .Value
End With
End With[/VBA]