Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass a dynamic array FROM a function TO a sub
Hi all, I'm having a lot of trouble trying to figure this one out...
I have 1 workbook with 2 sheets worksheet 1 has data in columns A and B (let's say 10 rows, but the number can vary) Using a function, I want to read that information into an array, and pass that array to SUB xyz() sub xyz() will then select my second sheet, and paste the array in column C and D Try as I might, I can't get it to work; it fails on Ubound(positions). Holding my mouse pointer over Ubound(positions), I get a "subscript out of range" error. Can someone please tell me what I'm doing wrong? Thanks in advance, Al Sub xyz() Dim Positions() Call copyover sheets("2").select For i = 1 To UBound(positions) Range("C" & i) = positions(1, i) Range("D" & i) = positions(1, i) Next i End Sub --------------------------------------------------------- Function copyover(positions) As Variant() pfrow = 1 Dim holdings() Range("A1").Select While Range("A" & pfrow).Value < "" ReDim Preserve holdings(2, pfrow) holdings(1, pfrow) = Range("A" & pfrow) holdings(2, pfrow) = Range("B" & pfrow) pfrow = pfrow + 1 Wend positions = holdings End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass an array | Excel Programming | |||
How to pass arguments to an Excel macro using Dynamic Data Exchang | Excel Programming | |||
Dynamic multiple-condition array function with more than one name | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Pass an array to Rank | Excel Worksheet Functions |