Transpose

[DCondie]

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

 

[Bernie Deitrick]

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

 

[Alan Beban]

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