Transpose
 

Hi,

How do I transpose formulas from a line to a colllumn?:)


--
Stephen_Rammer
------------------------------------------------------------------------
Stephen_Rammer's Profile: http://www.excelforum.com/member.php...o&userid=36850
View this thread: http://www.excelforum.com/showthread...hreadid=565563

Transpose
 

Did you try 'Edit'....'Copy'....'paste special'.....transpose?

"Stephen_Rammer" wrote:


Hi,

How do I transpose formulas from a line to a colllumn?:)


--
Stephen_Rammer
------------------------------------------------------------------------
Stephen_Rammer's Profile: http://www.excelforum.com/member.php...o&userid=36850
View this thread: http://www.excelforum.com/showthread...hreadid=565563

Transpose
 

Yes, it just returns a lot of REFS#


--
Stephen_Rammer
------------------------------------------------------------------------
Stephen_Rammer's Profile: http://www.excelforum.com/member.php...o&userid=36850
View this thread: http://www.excelforum.com/showthread...hreadid=565563

Transpose
 

Can you give an example of the data you are trying to move? Is it just
formulas or are youmoving formulas and cells that the formulas refer to?

"Stephen_Rammer" wrote:


Yes, it just returns a lot of REFS#


--
Stephen_Rammer
------------------------------------------------------------------------
Stephen_Rammer's Profile: http://www.excelforum.com/member.php...o&userid=36850
View this thread: http://www.excelforum.com/showthread...hreadid=565563

Transpose
 

First do an edit / replace and replace = with %%
Then copy and paste special transpose
Then do an edit / replace and replace %% with =

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Stephen_Rammer"
<Stephen_Rammer.2blzlg_1154011810.1741@excelforu m-nospam.com wrote in
message news:Stephen_Rammer.2blzlg_1154011810.1741@excelfo rum-nospam.com...

Yes, it just returns a lot of REFS#


--
Stephen_Rammer
------------------------------------------------------------------------
Stephen_Rammer's Profile:
http://www.excelforum.com/member.php...o&userid=36850
View this thread: http://www.excelforum.com/showthread...hreadid=565563

Transpose
 

Assuming your formulas are in Row 1

In a cell out of the used range enter this formula

=INDEX($1:$1,ROWS($1:1))

Copy down the column.

The actual formulas are not transposed, just the results of those formulas.

To copy and transpose the actual formulas would require VBA macro.

Cannot attribute code because I forgot to add the originator's name.

Apologies to whomever.

Sub Transpose_Formulas()
Dim sRange As Range, dCell As Range
Dim sCell As Range, i As Integer, j As Integer
Dim Str As String

'get input ranges. default box is filled by use of text
'variable set to the selected address
Str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set sRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", Type:=8,
default:=Str)
If Not sRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = sRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)

'loop through all cells, working backward to the top left cell
For i = sRange.Rows.Count - 1 To 0 Step -1
For j = sRange.Columns.Count - 1 To 0 Step -1
If i 0 Or j 0 Then
'do this for all but the first cell
sCell.Offset(i, j).Cut _
Destination:=dCell.Offset(j, i)

Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next j
Next i
End If
End If

End Sub



Gord Dibben MS Excel MVP

On Thu, 27 Jul 2006 09:20:44 -0400, Stephen_Rammer
<Stephen_Rammer.2blvnq_1154006709.6638@excelforu m-nospam.com wrote:


Hi,

How do I transpose formulas from a line to a colllumn?:)