How to pass a dynamic array FROM a function TO a sub

A

Al

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top