ExcelBanter - View Single Post

Alan Beban[_2_]

wrote:
Extra Question:
I then want to paste all Array data into one long column in the
worksheet.
Can this be done by pasting an array column by the next array column
or not.
Again I know i can do it with nested FOR statements easily but want to
understand alternative methods there are of inputting and retreiving
sets of data from Arrays.

Not clear exactly what the chronology of events is, and what's going on
in between filling rows in the array, and in between having the filled
multi-row array and then transferring it to a single column on the
worksheet; but in general you might want to consider the functions in
the freely downloadable file at http:/home.pacbell.net/beban. If they
are available to your workbook you can add a horizontal range of data
(assigned to a variable "rng" for example) as a row of data in the first
columns of, for example, row 3 of the array (let's say temparray()),
with something like

ReplaceSubArray temparray, rng, 3, 1

And you can transfer a 2-D array column by column to a single column on
the worksheet (let's say Column H) with something like

arr = ArrayReshape(tempArray, ArrayCount(tempArray), 1, "c")
Range("H1:H" & ArrayCount(tempArray)).Value = arr

If it were clearer what the flow of events is in your procedure, it
would be possible to provide inline code to do what the above UDF's
(ReplaceSubArray and ArrayReshape) would do.

Alan Beban