Excel Array Formulas
Here is an example for you to work on
Function myfunc()
Dim ary
Dim tmp
Dim rng As Range
Dim i As Long, j As Long
Set rng = Application.Caller
ReDim tmp(1 To rng.Rows.Count, 1 To rng.Columns.Count)
'<=== add your code to generate array of data
'the next line is just a simple example
ary = [{1,"a","1a";2,"b","2b";3,"c","3c"}]
For i = LBound(ary, 1) To UBound(ary, 1)
For j = LBound(ary, 2) To UBound(ary, 2)
tmp(i, j) = ary(i, j)
Next j
Next i
For i = LBound(tmp, 1) To UBound(tmp, 1)
For j = UBound(ary, 2) + 1 To UBound(tmp, 2)
tmp(i, j) = ""
Next j
Next i
For j = LBound(tmp, 2) To UBound(tmp, 2)
For i = UBound(ary, 1) + 1 To UBound(tmp, 1)
tmp(i, j) = ""
Next i
Next j
myfunc = tmp
End Function
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
wrote in message
oups.com...
I've created a simple formula that returns an array. This array is of
unkown number of rows and columns. Currently I enter the formula into
a cell, then drag and select a large numner of cells and use ctrl +
shift + enter to have the cells populate the array formula results.
The problem is that i don't know how far i have to drag. it is a lot
of trial and error. Is there anyway to have a function return an array
and populate it to the correct number of cells? Thanks for your help.
-Andrew
|