changing lists of cells to absolute values using a macro

  • Thread starter Thread starter Allerum30
  • Start date Start date
A

Allerum30

I need to change several lists of cells from normal(ie. cell A1 shows the
value in cell C4) I need to change that value to $C$4. I do it by clicking
the cell and hitting F2 then F4. For a list of 20 cells. When I try to use a
macro to perform this function(using relative reference or not)all it does is
give me a list of 20 $C$4's. what am I doing wrong?
 
Your macro probably says something like
cellFormulaR1C1 = R4C3

Which, of course, will always generate C4.

Instead of that, use this short macro to do what you want.

Sub AbsoluteReference()
'Modify range size in next line accordingly
For Each cell In Range("C4:C24")
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1,
xlAbsolute)
Next cell
End Sub
 
Back
Top