Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Array Formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Array Formulas
Use the formula to return the array to an array variable in VBA. Then you
can use Ubound and Lbound to determine how many cells in the worksheet should be filled. -- Gary's Student gsnu200704 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative program to Excel for array formulas | Excel Discussion (Misc queries) | |||
Array formulas not working in Excel 2003. | Excel Worksheet Functions | |||
Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions | |||
excel 2000 - array formulas | Excel Worksheet Functions | |||
Excel array formulas | Excel Discussion (Misc queries) |