在绝对和相对之间更改单元格引用 [英] Change Cell Reference between Absolute and Relative

问题描述

我想编写一个宏，它遍历选择单元格中所有单元格引用公式，并将它们更改为绝对或相对. /p>

是否有一个可以更改此格式的格式化变量或已经执行此操作的函数(类似于按F4所做的操作)但是作为宏.

解决方案

您可以使用ConvertFormula方法.

第4个参数确定其绝对值. 1将其设置为绝对值，4将其设置为相对值.根据对此答案的评论，如果您正在寻找混合参考，那么它有点复杂.但是阅读您的问题和评论后，我认为这不是您想要的.

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)

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.

解决方案

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)

这篇关于在绝对和相对之间更改单元格引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！