Excel VBA公式转代码后MATCH函数单元格引用锁定问题求助

Let's break down what's happening and get this sorted out for you!

The Root Cause #

When you use Range.FormulaArray to assign a formula to a multi-cell range (like G10:Gxx), Excel treats this as a single multi-cell array formula. Any relative references in the formula are anchored to the top-left cell of the range (G10 in your case). That's why RC[-6] (which maps to A10 for G10) stays locked to A10 for every cell in the range—instead of updating to A11, A12, etc., as you expected.

This is different from manually dragging the formula, where each cell gets its own independent array formula with references adjusted to its position.

Solution 1: Loop Through Each Cell (Preserve Array Formula Behavior) #

We'll assign the array formula to each cell individually, so relative references adjust correctly for every row:

Dim LastRowColumnA As Long
Dim targetCell As Range

' Fix: Use Sheet1.Rows.Count to avoid relying on the active sheet
LastRowColumnA = Sheet1.Cells(Sheet1.Rows.Count, 4).End(xlUp).Row

' Loop through each cell in the target range
For Each targetCell In Sheet1.Range("G10:G" & LastRowColumnA)
    targetCell.FormulaArray = "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C7&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-6],Table1!R6,0)), """")"
Next targetCell

Solution 2: Switch to a Non-Array Formula (Faster, No Loop Needed) #

If you want to avoid looping entirely, you can rewrite the formula using SUMPRODUCT to handle the row lookup without needing an array formula. This lets you assign the formula to the entire range in one go, with references updating automatically:

Dim LastRowColumnA As Long
LastRowColumnA = Sheet1.Cells(Sheet1.Rows.Count, 4).End(xlUp).Row

Sheet1.Range("G10:G" & LastRowColumnA).Formula = _
"=IFERROR(INDEX(Table1!$A$1:$DP$27,SUMPRODUCT((Table1!$E:$E=$G$9)*(Table1!$F:$F=$E$4)*ROW(Table1!$A$1:$A$27)),MATCH(A10,Table1!$6:$6,0)), """")"

This version works just like your original formula but runs more efficiently, especially with large datasets.

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