Hi Ron,
Thank you. This looks promising. I'll work on this and see if I can make it
work for me.
keith

"Ron Rosenfeld" wrote:

On Wed, 24 Feb 2010 05:13:02 -0800, Keith
wrote:

Hi,
It has been years that Ive been looking for the following, and Im still
looking.
I have a function that will be used in a spreadsheet. I want to have one
range (Range1) be the input range, with the result provided in a second range
(Range2). The function call would be this€¦.

Public function MyRangesFunction (Range1 as range, Range2 as range)

Some code here


MyRangesFunction = some_value (wish it could be a specified range)

End function


The only way Ive been able to do this is via a subroutine and some fancy
coding that speaks directly to the active sheet, etc.

Is there any way that I can easily return a range from a function?

Thank you,

Keith

If I understand you correctly, you want your results returned into a number of
different cells.

In order to do that, with a function, you'll need to write it as an array
function (e.g. similar to LINEST which returns its results into an array of
cells).

When you enter the function, you'll need to either enter it as an array, over
the cells into which you want to have the results or; if the result cells are
not contiguous, then as multiple entries into those cells using the INDEX
function to return the answer you require.

So something like:

======================================
Public Function MyRangesFunction(InputDataRange as Range) as Variant
dim vResults()

somecode that returns e.g. 10 results

redim vResults(9)
for i = 0 to 9
vResults(i) = your_code_result i
next i

MyrangesFunction = vResults
End Function
========================================

You then array-enter this function into your "Range2" (or OutputDataRange)

--ron
.