Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?:) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help using Transpose | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
Transpose into a _working_ transposed array | Excel Discussion (Misc queries) | |||
TRANSPOSE() | Excel Worksheet Functions |