Excel 2002: Any quick way for transposing formulas?

[Mr. Low]

Dear Sir

I understand that I need to change the cell address in a formula from
relative to absolute before I could transpose it.

E.g.

A B C
1 Code Date Amount
2 M236 xxx 250
3 K254 xxx 360
4 T841 xxx 550
5 K103 xxx 750
6 T145 xxx 320

From To
=C2 =$C$2
=C3 =$C$3
=C4 =$C$4
=C5 =$C$5
=C6 =$C$6

Transposing
=$C$2 =$C$3 =$C$4 =$C$5 =$C$6

My problem is if I copy a relative cell, I do not need to change the row or
column numbers, but I need to enter the $ sign for each cell.

If I choose to copy an absolute formula cell, I would need to enter the row
or column numbers.

May I know if there is a quick way for changing a formula from absolute to
relative without having to enter $ sign or changing the row or column
numbers manually ?

Thanks

Low

 

[Gav123]

Hi,

Not sure if this will work in XL2002 but...

Select your range and do a find and replace (CTRL+H) and in Find What enter
=C and Replace with =$C$

Thsi works in XL2003

Hope this helps,

Gav.

 

[Gary''s Student]

First enter Dibben's macro:

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Then select the cells and run the macro.

 

[Gord Dibben]

Or don't use that macro, rather use this one from I believe John
Walkenbach,,,,,,,,,maybe Tom Ogilvy.

You pick the range to transpose and the range to paste to.

Transposes without adding the Absolute $ signs.

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

 

[Mr. Low]

Dear Sir,

Thanks for the code.

Low