vba - Change Cell Reference between Absolute and Relative

Question

I want to write a macro which goes through all cell references in a selection's cells formula and changes them to absolute or relative.

Is there a formatting variable which can change this or a function which does this already (similar to what pressing F4 does) but as a macro.

Answer 1

You can use ConvertFormula method.

4th Parameter determines whether its absolute or not. 1 sets it to absolute and 4 sets it to relative. As per one comment to this answer, if you are looking for mixed references, then its bit complex. but reading your question and comments, I think that's not what you are after.

Examples:
'/ Set it to absolute
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 1)

'/ Set it to relative
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 4)