If a function is to return an array of values to more than one cell,
the function must be entered into multiple cells as an array formula,
using CTRL SHIFT ENTER. There is no way around this. See
http://www.cpearson.com/Excel/Return...ysFromVBA.aspx for more
information and examples about returning arrays from user defined
functions written in VB/VBA.

One change I would make to your code is to take the
ReDim Preserve arrV(c)
lines out of the loops. Preserve is an expensive operation and can
cause performance issues. Instead of using Preserve inside the loops,
ReDim the array to the maximum possible size before the loop, fill the
elements within the loop, and then use Preserve after the loop to
shrink the array down to the actual used size.



Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 1 Dec 2008 05:20:50 -0800 (PST), PauloD
wrote:

Hi, I am trying to design a formula that returns a kind of score of
your input. I have attached this spread sheet for ilustration
http://spreadsheets.google.com/pub?k...EVrvj9aEv9xmCA

the user formula has 2 array inputs (B3:B11, C3:C11)
i need it to answer the stuff in yellow.

so far I have been a able to load up 2 arrays with 9 layers of
information like

arr(0) grape and arr1(0) 38
arr(1) grape and arr1(1) 45
arr(2) kiwi and arr1(2) 58
.
arr(9) banana and arr1(9) 75

I need help on doing the sumIF on the values across the arrays
and later on sorting and deleting the repeated data

and at last but not least.
if there is a way wher the UDF could return the range without or
automating
the Crtl+Shift+ENTER
here is the code that I have right now.

Function test(a As Range, b As Range) As String()

Dim Cell As Range
Dim arrN() As String
Dim arrV() As Long
Dim arrF() As String
Dim c As Integer
Dim newC As Integer
Dim Hcnt As Integer
Hcnt = a.Count - 1

c = 0
For Each Cell In a
ReDim Preserve arrN(c)
arrN(c) = Cell.Value
c = c + 1
Next
c = 0
For c = 0 To Hcnt Step 1

c = 0
For Each Cell In b
ReDim Preserve arrV(c)
arrV(c) = Cell.Value
c = c + 1
Next

c = 0
For c = 0 To Hcnt Step 1
ReDim Preserve arrF(Hcnt)
arrF(c) = arrN(c) & " - " & arrV(c)
Next

test = arrF
I would apretiate any help