在Excel公式(VBA)中从绝对引用更改为相对引用

Question

不幸的是，我在其他问题中找不到以下问题的答案——我的问题与复制和粘贴使用相对单元格引用而不是动态单元格引用的公式有关。

我正在处理的工作簿的一般问题是它包含几个不同的工作表，具有潜在的动态范围变化。为了给出更好的大纲：

• 公式必须所在的列基于偏移单元格——我不能给它一个静态值——并从第 2 行开始;
• 公式本身如下：=CONCATENATE(LEFT(AA2,13), “:”, RIGHT(AA2,5)(要连接的值将始终出现在 AA 列中)

宏应该将公式插入偏移单元格中，将其复制并粘贴到整个列中，因此第 3 行中的单元格引用单元格 AA3，依此类推：

Sub Copy1()
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(, 1).Select
Dim rng as Range
Set rng = ActiveCell
rng.Select
rng.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(R2C27,13), "":"", RIGHT(R2C27,5))"
rng.Offset(1, 0).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PasteSpecial
Selection.EntireColumn.Select
Application.CutCopyMode = False
End Sub

问题是 – 在尝试插入单元格的确切名称时，宏会用以下内容填充它：

=CONCATENATE(LEFT(‘AA2’,13), "":"", RIGHT(‘AA2’,5))

由于引号，公式不起作用。

使用引用R2C27结果是复制绝对值，因此列中的每个单元格都引用单元格AA2。

是否有可能使其创建，复制和粘贴相对引用而不是绝对引用？

Answer 1

通常不需要R1C1引用，因为如果您复制+粘贴公式或将其分配给多个单元格，则会自动调整相对引用。例如：

Range("A2:A9").Formula = "=CONCATENATE(LEFT(AA2,13), "":"", RIGHT(AA2,5)"

Answer 2

如果我知道您希望列引用始终$AA(绝对)，而行引用是相对的。你可以试试这个：

ActiveCell.Formula = "=CONCATENATE(LEFT($AA2,13), "":"", RIGHT($AA2,5))"

然后自动填充将自动调整行号，同时将列保持在"$AA"。

也就是说，重构你的代码以摆脱select的东西。看看除了使用"显式引用"之外，它会有多短(只需将"Sheet1"替换为工作表的实际名称)：

Sub Copy1()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1").End(xlToRight).Offset(1, 1)
rng.Formula = "=CONCATENATE(LEFT($AA2,13), "":"", RIGHT($AA2,5))"
rng.Copy rng.Parent.Range(rng, rng.End(xlDown))
End Sub