VBA中使用FormulaR1C1能否引用其他工作表整列？

问题背景 #

需求为在Sheet1的C1单元格写入公式=VLOOKUP(A1,Sheet2!A:B,2,FALSE)，编写5段测试代码后仅test-5可正常运行，测试代码如下：

Sub test()

'test-1
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!c[1]:c[2],2,false)"
'=VLOOKUP(A1,Sheet2!D:E,2,FALSE) ---> 公式错误

'test-2
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!A:B,2,false)"
'=VLOOKUP(A1,Sheet2!A:(B),2,FALSE) ---> 公式错误

'test-3
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!$A:$B,2,false)"
'抛出错误1004

'test-4
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!r[1]c[1]:r[100]c[2],2,false)"
'=VLOOKUP(A1,Sheet2!D2:E101,2,FALSE) ---> 公式错误

'test-5
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!r1c1:r100c2,2,false)"
'=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) ---> 公式可运行，但范围限定了固定行数

End Sub

待解答疑问 #

• 使用FormulaR1C1时是否支持引用其他工作表的整列？若不支持说明原因，若支持给出正确实现方法。
• test4和test5的引用差异原因：test5中r1c1:r100c2可正确返回带$绝对引用标识的$A$1:$B$100范围，为何test4中r[1]c[1]:r[100]c[2]未返回无$标识的相对引用范围A1:B100，反而以Sheet1的C1单元格为基准偏移，引用到了D2:E101范围？

解答内容 #

1. FormulaR1C1支持跨工作表整列引用 #

之前测试代码报错全是写法不符合R1C1语法规则导致：
R1C1引用体系中，绝对引用整列的标准写法是C列序号，不需要加行标记，直接拼接工作表名即可实现跨表整列引用。

• test-1用了带方括号的相对偏移写法c[1]:c[2]，偏移基准是公式所在的C1单元格，列偏移1、2位自然指向D、E列，和Sheet2本身的列位置无关。
• test-2直接写A:B是A1格式的列写法，和FormulaR1C1要求的语法不兼容，所以解析后格式错乱。
• test-3写的$A:$B是A1格式的绝对列引用，和R1C1模式语法冲突，直接触发1004错误。

正确的跨表整列引用代码如下，写入后会自动解析为目标公式=VLOOKUP(A1,Sheet2!A:B,2,FALSE)：

Range("C1").FormulaR1C1 = "=VLOOKUP(RC[-2],'" & Sheet2.Name & "'!C1:C2,2,FALSE)"

2. 引用差异来自R1C1相对/绝对引用的基准规则 #

R1C1引用的核心逻辑非常明确：

• 不带方括号的R数字C数字是绝对引用，直接定位工作表的固定坐标，和公式所在单元格的位置没有关系。比如R1C1固定指向工作表第1行第1列也就是A1单元格，因此test5里的r1c1:r100c2是固定指向Sheet2的A1:B100区域，解析后自然带$绝对引用标识。
• 带方括号的R[偏移值]C[偏移值]是相对引用，偏移量的计算基准永远是当前写入公式的单元格，哪怕引用的是其他工作表的区域，偏移计算也不会换基准。test4里公式写在C1单元格，对应R1C1坐标体系里的R1C3：
  • R[1] = 当前行号1 + 偏移量1 = 第2行
  • C[1] = 当前列号3 + 偏移量1 = 第4列（D列）
  • R[100] = 当前行号1 + 偏移量100 = 第101行
  • C[2] = 当前列号3 + 偏移量2 = 第5列（E列）
    最终解析出来的区域自然是Sheet2的D2:E101，和预期的A1:B100不符。

实操提示：跨表写R1C1公式时，如果不是刻意要做相对偏移的区域引用，优先用不带方括号的绝对R1C1写法，或者直接用Formula属性写A1格式的公式，能避免大部分偏移错位问题。

内容的提问来源于stack exchange，提问作者karma