Transfering VBA Array Column Range to Excel Column Range

[ExcelMonkey]

I know that I can make excel ranges = to other excel ranges if they have
the same dimensions. Assume ActiveColumn is a 100X1 (r,c) excel array.
Below I can make this range equal to an offset of itself


Range("ActiveColumn") = Range("ActiveColumn").Offset(0, 1).Value

But what happens if I want to make that range equal to a part of a VBA
array. Say the VBA Array is called DataArray. And also assume that
DataArray is 100x100. Is there any way I can make ActiveColumn = to a
portion of the array without using a Loop?

 

[Tushar Mehta]

A one way transfer, i.e., from the array to the XL range and if want to
pick the vector that corresponds to the outer index, the following will
work:

Range("A1").Resize(100, 1) = .Transpose(.Index(x, 1))

It transfers the first row of the 2D array x into 100 cells in column A
of the worksheet starting with A1.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Alan Beban]

You can't do it without executing loops; but if the functions in the
freely downloadable file at http://home.pacbell.net/beban are available
to your workbook, you can use the looping that is prewritten in the
those functions. For example, with 1 to 100 in Range("a1:a100"), the
following will transfer the elements of that range to the third "column"
of the array:

Sub testIt()
Dim rng As Range
Set rng = Range("A1:A100")
Dim arr() As Integer
ReDim arr(1 To 100, 1 To 100)
ReplaceSubArray arr, rng, 1, 3
End Sub

It replaces the portion of arr starting at "row" 1, "column" 3 with the
elements of rng.

Alan Beban

 

[Alan Beban]

That won't work in Excel2000 and earlier for an x that is a 100x100
array, or any array of more than 5461 elements. And in any event, it
doesn't get at the OP's request for getting a range column into a VBA array.

Alan Beban

 

[Tushar Mehta]

The OP wrote, "Is there any way I can make ActiveColumn = to a portion
of the array without using a Loop?"
How does that translate to "it doesn't get at the OP's request for
getting a range column into a VBA array ?"

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Alan Beban]

Sorry; I guess I misinterpreted it (though I'm still not 100% sure). In
any event:

Range("A1").Resize(100, 1) = .Transpose(.Index(x, 1)) will fail in
Excel2000 and earlier if x contains more than 5461 elements; the Index
function won't accommodate it. It also, in Excel2000, returned an error
message that Index and Transpose were invalid or unqualified references.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to the workbook, the
following will work in Excel2000 and earlier (it uses loops, but they
are prewritten into the RowVector function):

Range("A1").Resize(100, 1) = Application.Transpose(RowVector(x, 1))

Alan Beban