Transpose dynamically

[rasmus79]

I am hoping to "recreate" the transposing of text and numbers that I can do with "COPY" - PASTE SPECIAL (And then selecting transpose)
IE. I have lots of cells with data horizontally on ROW1, but I would like a copy of them along collumn A (Starting on ex A3)
Does anyone know how to do this?
Thanks
I tried combining a sum and index and match, but that didnt work for text. I just want to return what is in the cell. No summing or multiplying or anything like that.

 

[yankee428]

I am sure there is a better way to do this, but here is one solution.

This will take the entire row from B to IV for the current active cell and tranpose it into Column A.

Sub trans()
Dim r As Long
Dim col As Long
Dim content

r = ActiveCell.Row
For col = 2 To 256
content = Cells(r, col).Value
Cells(col, 1) = content
Next
End Sub

 

[rasmus79]

Thanks - I have to get better at using macros. I do not feel too secure on how to write those.
Does anyone have a good way of doing this just through formulas?

 

[fairwinds]

In A3:

=INDEX($1:$1,ROW()-ROW($A$3))

Drag down.

 

[rasmus79]

Fairwinds! You did it again.
I tried to use the formula you helped me with earlier, but I got stuck on the MATCH function rather than index.
Thanks again!

 

[howzat]

Just for interest sakes, there is the = transpose() formula.

This needs to be array entered.