需求：将指定Excel VBA公式转换为公式内单元格及工作表动态引用

Got it, let's tackle converting that hard-coded formula in your VBA to use dynamic references so it automatically adapts when your data grows or shrinks. No more updating those fixed row/column numbers every time you add data to the DigiFull sheet!

Option 1: Use INDEX + COUNTA for Dynamic Ranges #

This method calculates the actual last row/column with data in your DigiFull sheet, so the formula always references the full active data set. Here's the modified VBA code:

ActiveCell.Offset(1, 1).Formula = _
    "=INDEX(DigiFull!$A$1:INDEX(DigiFull!$DB:$DB,COUNTA(DigiFull!$A:$A))," & _
    "MATCH($E2,DigiFull!$A$1:INDEX(DigiFull!$A:$A,COUNTA(DigiFull!$A:$A)),0)," & _
    "MATCH(M2,DigiFull!$A$1:INDEX(DigiFull!$1:$1,COUNTA(DigiFull!$1:$1)),1))"

Let's break down the key changes:

• Dynamic data range: Replaced DigiFull!$A$1:$DB$855 with DigiFull!$A$1:INDEX(DigiFull!$DB:$DB,COUNTA(DigiFull!$A:$A)). COUNTA(DigiFull!$A:$A) counts non-empty cells in column A to get the last row of data, and INDEX pins that row in column DB—so the range expands/contracts with your data automatically.
• Dynamic lookup column: Changed DigiFull!$A$1:$A$855 to DigiFull!$A$1:INDEX(DigiFull!$A:$A,COUNTA(DigiFull!$A:$A)) to ensure MATCH always searches the full set of values in column A.
• Dynamic header row: Swapped DigiFull!$A$1:$DB$1 for DigiFull!$A$1:INDEX(DigiFull!$1:$1,COUNTA(DigiFull!$1:$1)) to capture all header columns, even if you add new ones later.

Option 2: Use Excel Tables (Structured References) – Recommended #

If you convert your DigiFull data into an Excel Table (ListObject), you get built-in dynamic range handling with cleaner, more readable formulas. Here's how:

1. Select your data in DigiFull and press Ctrl+T to convert it to a table (check "My table has headers" if applicable). Let's name the table tblDigiFull (you can rename it in the Table Design tab).
2. Update your VBA code to use structured references:

ActiveCell.Offset(1, 1).Formula = "=INDEX(tblDigiFull,MATCH($E2,tblDigiFull[#All],0),MATCH(M2,tblDigiFull[#Headers],1))"

This approach is preferable because:

• The table automatically expands when you add new rows/columns—no need to adjust formulas or VBA code.
• Structured references like tblDigiFull[#Headers] make the formula far more readable than raw cell ranges.

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