absolute reference

[Guest]

I have entered a formula in a cell and have copied it to several other
adjacent cells. Now I want to change all the formulas to absolute references
so I can sort the list. How can I change a group of cells all at once rather
than editing each cell and adding the $'s.

Mike

 

[Fred Smith]

Sorting has nothing to do with relative vs absolute addresses.

Tell us what you're trying to do. There has to be a better way than manually
entering the dollar signs.

 

[Guest]

You can sort without changing to absolute references but if you want to do it
then right click your sheet tab, view code and paste this in. Select all the
cells you want to change and run this:-

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


Mike

 

[Guest]

This basically answers my question that 2007 does not have something built in
to do this. For 2003 I was using a third party add-in that did this.

Here's what I'm trying to do.
Col A has a list of text words
Col b has number values
Col C has number values
Col A is copied to Col D
Col E has Col C divided by col B with an average added at the bottom.
I Now want to sort Col D and E, sorting on col E and including the average.

The add-in I was using let me select all the formulas in Col E and change
all the cell references in those cells to absolute reference.

I was hoping that Excel 2007 had this ability.
So if not, I'll use the macro or the add-in.
Mike