Reply to thread

Message

[QUOTE="candy.chiu.ad, post: 7416158"]
I have the following data

Range1 (1x4)
id	1	3	5

Range2 (3x1)
2
4
6

The goal is very simple.  I want to extract the numbers in Range1, then sum product them with Range2.

To extract numbers - Index(Range1, 1, {2,3,4})
Then multiply - MMULT(Index(Range1, 1, {2,3,4}), Range2)

The problem is since the result is single-cell, even I instructed cell to use an array formula, it defaulted back to regular formula.  As a result, Index(Range1, 1, {2,3,4}) returned a 1x1 array instead of a 1x3 array, causing MMULT to fail.

Because of this, TRANSPOSE + SUMPRODUCT didn't work neither.

I then thought about creating an IndexWrapper function in VBA to force it to return an array.

' As a Test
Public Function IndexWrapper (arr As range) As Variant()

Dim cols(3) As Double
cols(1) = 2
cols(2) = 3
cols(3) = 4

IndexWrapper = WorksheetFunction.Index(arr, 1, cols)

End Function

WorksheetFunction.Index doesn't seem to take the cols into consideration.  The entire row is returned.

Anyone has a good solution for this problem?  I am trying not to create an one off work around.  Thanks.
[/QUOTE]

Verification