Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose
Working in Excel 2003
I am attempting to transpose row data of variable length into an array and write the contents of the array into a column in Sheet 2 starting at a specific row in the column. The placement of data in the specific row in Sheet 2 will be determined later by a date associated with the initial cell in the row data. The subprocedure listed below is an attempt to process the data from one row. Later I'll program the looping procedure. The problem is two fold: 1)I do not have the correct coding to copy the contents of the array to Sheet 2; 2)I cannot determine whether my code to convert the row data into the array is correct. Public Sub Transpose() Dim D As Integer 'Loop number to read individual row data D = 2 Dim I As Integer 'Loop number for copying colIndex into Array Dim J As Integer 'Loop number for copying rowIndex into Array Dim transArray() As Variant Dim numRows As Integer Dim numColumns As Integer Dim colIndex As Integer Dim rowIndex As Integer Dim inputRange As Range Set inputRange = ActiveWindow.Selection colIndex = inputRange.Column rowIndex = inputRange.Row 'Start loop to read data With Worksheets("Sheet1").Cells(D, 3) .Resize(1, .End(xlToRight).Column - .Column).Selection 'Get the size of the data block numRows = inputRange.Rows.Count numColumns = inputRange.Columns.Count ReDim transArray(numRows - 1, numColumns - 1) 'Copy Values into the array For I = colIndex To numColumns = colIndex - 1 For J = rowIndex To numRows + rowIndex - 1 transArray(J - rowIndex, I - colIndex) = Cells(J, I).Value Next J Next I 'Copy the array to Sheet2 to a specific column in transposed form inputRange:=Worksheets("Sheet2").Cells(D, 10) For I = colIndex To numRows = colIndex - 1 For J = rowIndex To numColumns + rowIndex - 1 Cells(J, I).Value = transArray(I - colIndex, J - rowIndex) Next J Next I Cells(rowIndex, colIndex).Select End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose
DCondie,
How about? Dim D As Integer D = 2 Selection.Copy Worksheets("Sheet2").Activate Cells(D, 10).Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True HTH, Bernie MS Excel MVP "DCondie" wrote in message om... Working in Excel 2003 I am attempting to transpose row data of variable length into an array and write the contents of the array into a column in Sheet 2 starting at a specific row in the column. The placement of data in the specific row in Sheet 2 will be determined later by a date associated with the initial cell in the row data. The subprocedure listed below is an attempt to process the data from one row. Later I'll program the looping procedure. The problem is two fold: 1)I do not have the correct coding to copy the contents of the array to Sheet 2; 2)I cannot determine whether my code to convert the row data into the array is correct. Public Sub Transpose() Dim D As Integer 'Loop number to read individual row data D = 2 Dim I As Integer 'Loop number for copying colIndex into Array Dim J As Integer 'Loop number for copying rowIndex into Array Dim transArray() As Variant Dim numRows As Integer Dim numColumns As Integer Dim colIndex As Integer Dim rowIndex As Integer Dim inputRange As Range Set inputRange = ActiveWindow.Selection colIndex = inputRange.Column rowIndex = inputRange.Row 'Start loop to read data With Worksheets("Sheet1").Cells(D, 3) .Resize(1, .End(xlToRight).Column - .Column).Selection 'Get the size of the data block numRows = inputRange.Rows.Count numColumns = inputRange.Columns.Count ReDim transArray(numRows - 1, numColumns - 1) 'Copy Values into the array For I = colIndex To numColumns = colIndex - 1 For J = rowIndex To numRows + rowIndex - 1 transArray(J - rowIndex, I - colIndex) = Cells(J, I).Value Next J Next I 'Copy the array to Sheet2 to a specific column in transposed form inputRange:=Worksheets("Sheet2").Cells(D, 10) For I = colIndex To numRows = colIndex - 1 For J = rowIndex To numColumns + rowIndex - 1 Cells(J, I).Value = transArray(I - colIndex, J - rowIndex) Next J Next I Cells(rowIndex, colIndex).Select End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose
DCondie wrote:
Working in Excel 2003 I am attempting to transpose row data of variable length into an array and write the contents of the array into a column in Sheet 2 starting at a specific row in the column. The placement of data in the specific row in Sheet 2 will be determined later by a date associated with the initial cell in the row data. The subprocedure listed below is an attempt to process the data from one row. Later I'll program the looping procedure. The problem is two fold: 1)I do not have the correct coding to copy the contents of the array to Sheet 2; 2)I cannot determine whether my code to convert the row data into the array is correct. Public Sub Transpose() Dim D As Integer 'Loop number to read individual row data D = 2 Dim I As Integer 'Loop number for copying colIndex into Array Dim J As Integer 'Loop number for copying rowIndex into Array Dim transArray() As Variant Dim numRows As Integer Dim numColumns As Integer Dim colIndex As Integer Dim rowIndex As Integer Dim inputRange As Range Set inputRange = ActiveWindow.Selection colIndex = inputRange.Column rowIndex = inputRange.Row 'Start loop to read data With Worksheets("Sheet1").Cells(D, 3) .Resize(1, .End(xlToRight).Column - .Column).Selection 'Get the size of the data block numRows = inputRange.Rows.Count numColumns = inputRange.Columns.Count ReDim transArray(numRows - 1, numColumns - 1) 'Copy Values into the array For I = colIndex To numColumns = colIndex - 1 For J = rowIndex To numRows + rowIndex - 1 transArray(J - rowIndex, I - colIndex) = Cells(J, I).Value Next J Next I 'Copy the array to Sheet2 to a specific column in transposed form inputRange:=Worksheets("Sheet2").Cells(D, 10) For I = colIndex To numRows = colIndex - 1 For J = rowIndex To numColumns + rowIndex - 1 Cells(J, I).Value = transArray(I - colIndex, J - rowIndex) Next J Next I Cells(rowIndex, colIndex).Select End With End Sub I can't tell what it is you're trying to copy to where, but unless I misunderstand, consider forgetting the loops and using something like transArray = inputRange and then Worksheets("Sheet2").Range([upper left cell]).resize(UBound(transArray)).Value = Application.Transpose(transArray) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose | Excel Worksheet Functions | |||
Transpose from Col to row | Excel Discussion (Misc queries) | |||
Transpose from Col to row | Excel Worksheet Functions | |||
Transpose (?) | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |