VBA循环表格列时单元格引用异常变更问题排查与解决

问题原因 #

你在数组公式中使用了相对引用B5，Excel的公式引用规则会自动调整相对引用的行号，以保持公式所在单元格与引用单元格的相对位置不变。

在你的场景里，RegionSales表格的数据从第10行开始循环设置公式：

• 给C10设置公式时，Excel计算出C10与B5的行偏移量为5（10-5）；
• 由于是在ListObject范围内设置数组公式，Excel会将这个偏移量相对于表格数据区域的起始行（第10行）调整，导致引用从B5偏移为B1（10-9=1）；
• 直到循环到第14行（C14）时，行偏移量刚好对应到B5，因此从第5行开始引用恢复正常。

解决方法 #

将B5改为绝对引用$B$5，固定引用单元格位置，彻底避免Excel自动调整引用：

修改后的VBA代码片段：

For i = 10 to lastRow
    Range("C" & i).FormulaArray = "=IFERROR(INDEX(Sales[Net], Match(1, ($B$5 = Sales[Region])*(B" & i & " = Sales[ShopId]),0)),""""")"
Next i

你提到的拼接字符串B" & 5 & "的方式，本质只是写入固定的B5文本，但如果后续表格位置变动，仍可能出现相对引用问题，使用绝对引用$B$5是更规范、可靠的方案。

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