Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 dimension chart | Charts and Charting in Excel | |||
Matrix Dimension | Excel Worksheet Functions | |||
Getting excel array dimension | Excel Programming | |||
Applying Sum Function to 2nd Dimension of Array | Excel Programming | |||
single dimension array | Excel Programming |