Looping Through an Array and Transposing

[Rob]

If someone would be kind enough to help me, I would greatly appreciate
it.


I simply want to take a row of answers from 10 students and position
these answers horizontally instead of vertically on another
spreadsheet. Seems simple enough but for the life of me, I can not
figure it out.

So Student One's answers are presently listed like the following:

1
2
3
1
4
2
3
3

I would like to list on another spreadsheet like this:

Student One 12314233

Any help would be appreciated.

I have the following code, but it does not seem to work:

Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))
X = 1
Y = 1

ReDim Data(Source.Count)


LastColumn = ActiveSheet.UsedRange.Columns
(ActiveSheet.UsedRange.Columns.Count).Column

LastRow = ActiveSheet.UsedRange.Rows
(ActiveSheet.UsedRange.Rows.Count).Row


For Each K In Source

Data(X) = K.Value

X = X + 1
Next
Sheets(1).Select
Range("b3").Select

rw = 2
cl = 10

For cl = 10 To LastColumn

For rw = 2 To LastRow

Cells(rw, cl).Value = Data(rw)

X = X + 1




Next rw

Next cl


End Sub

 

[Per Jessen]

Hi

You are really complicating things to much....

Copy the range and use PasteSpecial => Transpose, in VBA someting like
this:

Sub aaa()
LastColumn = ActiveSheet.UsedRange.Columns
LastRow = ActiveSheet.UsedRange.Rows
Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))

Source.Copy
Sheets(1).Cells(3, 10).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Regards,
Per

 

[Rick Rothstein]

I think it can be simplified even more. First though, your LastColumn and
LastRow assignment statements are missing a .Count (dot-count) property call
at the end of each statement. However, I'm not entirely sure I would be
comfortable using the UsedRange to produce the ends of the data the OP wants
to transpose (I know you were just using what he posted, I just don't think
he was on the right track there). So, in my code, I assumed he actually
wanted the CurrentRegion range instead (although any range specification can
be used as necessary). Most of the code just sets things up... the workhorse
line is the single line at the end.

Sub TransposeRangeToAnotherSheet()

Dim Source As Range, Destination As Range
Const DestinationSheet = 2
Const DestinationStartCell = "D5"

Set Source = Cells(3, 10).CurrentRegion
Set Destination = Sheets(DestinationSheet).Range(DestinationStartCell)

Destination.Resize(Source.Columns.Count, Source.Rows.Count) = _
WorksheetFunction.Transpose(Source)

End Sub

--
Rick (MVP - Excel)


Hi

You are really complicating things to much....

Copy the range and use PasteSpecial => Transpose, in VBA someting like
this:

Sub aaa()
LastColumn = ActiveSheet.UsedRange.Columns
LastRow = ActiveSheet.UsedRange.Rows
Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))

Source.Copy
Sheets(1).Cells(3, 10).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Regards,
Per

 

[Rick Rothstein]

I should point out (before someone tries to "correct" it thinking I made a
mistake), the use of the source's column count in the destination's Resize
property's Row argument position and the source's row count in the
destination's Resize property's Column argument position are intentional.