Array transfer - 1 dimension v. 2 dimension
 

The following sub is supposed to read in column A and transfer to D,
then read in columns B and C and transfer to E and F.
In the first transfer, the value in A1 is transfered to all of column
D, in the second transfer B and C transfer correctly to E and F. Does
anybody know why the 2D transfer works, but the 1 dimension transfer
doesn't?

Sub foo()

Dim lastRow As Long
Dim i As Integer, j As Integer
Dim myRange()

lastRow = Cells(65536, 1).End(xlUp).Row
ReDim myRange(1 To lastRow)
For i = 1 To lastRow
myRange(i) = Cells(i, 1).Value
Next i
Range(Cells(1, 4), Cells(lastRow, 4)) = myRange

lastRow = Cells(65536, 2).End(xlUp).Row
ReDim myRange(1 To lastRow, 1 To 2)
For i = 1 To lastRow
For j = 1 To 2
myRange(i, j) = Cells(i, j + 1).Value
Next j
Next i
Range(Cells(1, 5), Cells(lastRow, 6)) = myRange

End Sub

Regards,
John Wolf

Array transfer - 1 dimension v. 2 dimension
 

In Range(Cells(1, 4), Cells(lastRow, 4)) = myRange, myRange is a
horizontal array and the range is vertical; substitute
Application.Transpose(myRange) for myRange.

But perhaps more importantly,consider the following:


Dim lastRow As Long
Dim myRange()

lastRow = Cells(65536, 1).End(xlUp).Row
myRange = Range(Cells(1, 1), Cells(lastRow, 1))
Range(Cells(1, 4), Cells(10, 4)) = myRange

lastRow = Cells(65536, 2).End(xlUp).Row
myRange = Range(Cells(1, 2), Cells(lastRow, 3))
Range(Cells(1, 5), Cells(lastRow, 6)) = myRange

Alan Beban

JWolf wrote:

The following sub is supposed to read in column A and transfer to D,
then read in columns B and C and transfer to E and F.
In the first transfer, the value in A1 is transfered to all of column
D, in the second transfer B and C transfer correctly to E and F. Does
anybody know why the 2D transfer works, but the 1 dimension transfer
doesn't?

Sub foo()

Dim lastRow As Long
Dim i As Integer, j As Integer
Dim myRange()

lastRow = Cells(65536, 1).End(xlUp).Row
ReDim myRange(1 To lastRow)
For i = 1 To lastRow
myRange(i) = Cells(i, 1).Value
Next i
Range(Cells(1, 4), Cells(lastRow, 4)) = myRange

lastRow = Cells(65536, 2).End(xlUp).Row
ReDim myRange(1 To lastRow, 1 To 2)
For i = 1 To lastRow
For j = 1 To 2
myRange(i, j) = Cells(i, j + 1).Value
Next j
Next i
Range(Cells(1, 5), Cells(lastRow, 6)) = myRange

End Sub

Regards,
John Wolf

Array transfer - 1 dimension v. 2 dimension
 

Hi John,

One easy way is

'*****
Sub foo()
Range("D:F").Value = Range("A:C").Value
End Sub
'*****

Otherwise the 1D array has to be transposed, you can try this slight change to
your code

'*****
Sub foo()

Dim lastRow As Long
Dim i As Integer, j As Integer
Dim myRange()

lastRow = Cells(65536, 1).End(xlUp).Row
ReDim myRange(1 To lastRow)
For i = 1 To lastRow
myRange(i) = Cells(i, 1).Value
Next i
'Range(Cells(1, 4), Cells(lastRow, 4)) = myRange
Range(Cells(1, 4), Cells(lastRow, 4)) = _
Application.WorksheetFunction.Transpose(myRange)

lastRow = Cells(65536, 2).End(xlUp).Row
ReDim myRange(1 To lastRow, 1 To 2)
For i = 1 To lastRow
For j = 1 To 2
myRange(i, j) = Cells(i, j + 1).Value
Next j
Next i
Range(Cells(1, 5), Cells(lastRow, 6)) = myRange

End Sub
'*****

Regards
Anders Silven


"JWolf" skrev i meddelandet
om...
The following sub is supposed to read in column A and transfer to D,
then read in columns B and C and transfer to E and F.
In the first transfer, the value in A1 is transfered to all of column
D, in the second transfer B and C transfer correctly to E and F. Does
anybody know why the 2D transfer works, but the 1 dimension transfer
doesn't?

Sub foo()

Dim lastRow As Long
Dim i As Integer, j As Integer
Dim myRange()

lastRow = Cells(65536, 1).End(xlUp).Row
ReDim myRange(1 To lastRow)
For i = 1 To lastRow
myRange(i) = Cells(i, 1).Value
Next i
Range(Cells(1, 4), Cells(lastRow, 4)) = myRange

lastRow = Cells(65536, 2).End(xlUp).Row
ReDim myRange(1 To lastRow, 1 To 2)
For i = 1 To lastRow
For j = 1 To 2
myRange(i, j) = Cells(i, j + 1).Value
Next j
Next i
Range(Cells(1, 5), Cells(lastRow, 6)) = myRange

End Sub

Regards,
John Wolf