MrExcel.com - Your One Stop for Excel Tips & Solutions

Copying cells using absolute references

Posted by Dave on September 13, 2001 5:05 AM

Does anyone know if there is an easy way to copy and paste using absolute cell references without having to go through and change each formula to include dollar signs in each cell reference?

For example, if a cell contains formula '=C2' is there a way to copy this cell to another location a keep the contents of the cell as '=C2'? I realise putting dollar signs on the formula would achieve this but I do not want to do this as a have hundreds of cells to copy in this way.

As an alternative solution, is there a way to absolute-ize all cell references many cells at once?

Thanks

Dave

Posted by Henry Root on September 13, 2001 5:58 AM


1. To manually copy the formula without changing the cell ref :

- select the cell
- highlight the formula in the formula bar
- copy
- exit the cell by pressing the down key
- select destination cell and paste


2. Macro to convert all formulas on the active worksheet to absolute refs :-

Sub Convert_All_Formulas_To_Absolute()
Dim cell As Range
For Each cell In Cells.SpecialCells(xlFormulas)
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute)
Next cell
End Sub


3. Macro to display all formulas on a worksheet as text so they can be copied without the cell refs changing :-

Sub Show_All_Formulas()
Cells.Replace What:="=", Replacement:="""=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End Sub


4. Macro to convert all formulas on a worksheet shown as text back to formulas :-

Sub Hide_All_Formulas()
Cells.Replace What:="""=", Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End Sub


Posted by Juan Pablo on September 13, 2001 7:05 AM

If you have =C1 and want to convert it to =$C$1, press F2 while in the cell, then press F4 one time (That should convert C2 to $C$2, press F4 again and you have C$1, once again and you have $C1, one last time and you have C1).

Say for example that you have a formula like this:

=IF(ISERROR(VLOOKUP(C1,Sheet2!A1:D35000,3,0),D1,VLOOKUP(C1,Sheet2!A1:D35000,3,0))

And you want to convert all (or some references) to absolute. Just hilite the formula (Or section that contains the references you want) and press F4 and you're done !!

Helps ?

Juan Pablo