VBA Formula Array报错求助：FormulaArray不适用于Range类

Hey there, let's break down why you're hitting this error and how to fix it. The core issue here boils down to unqualified cell references when running code outside the data's worksheet—let's unpack that:

Why Cells() References Are Causing the Problem #

When your code runs on a worksheet that's not where your data lives, using bare Cells(row, col) references tells Excel to look at the active worksheet (which might not be your data sheet) for the range. Array formulas are picky about exact range contexts, so this ambiguity throws the "FormulaArray doesn't function with Range class" error. Splitting the formula won't help because the root issue is missing a clear worksheet target for your data range.

Why R1C1 Style Works #

R1C1 references are inherently relative (unless you lock them with square brackets), so when you use this style, Excel automatically resolves the range relative to the cell where you're entering the formula—even if your code is on a different sheet. That's why it works without extra setup.

The Fix: Qualify Your Cell References #

To make A1-style array formulas work, you need to explicitly tell Excel which worksheet your data is on. Here's how to do it:

1. Define a worksheet object for your data source to keep things clean:

   Dim dataSheet As Worksheet
   Set dataSheet = ThisWorkbook.Sheets("YourDataSheetName") ' Replace with your actual sheet name
   

2. Build your array formula with qualified cell addresses using .Address to get the full A1 reference (including the sheet name if needed):

   ' Example: Sum values greater than 5 from a dynamic range in your data sheet
   With dataSheet
       ' Target cell is on the sheet where your code runs
       Range("B2").FormulaArray = "=SUM(IF(" & .Cells(1, 1).Address & ":" & .Cells(10, 1).Address & ">5, " & .Cells(1, 1).Address & ":" & .Cells(10, 1).Address & ", 0))"
   End With
   

3. Alternative: Use sheet-qualified references directly if you prefer:

   Range("B2").FormulaArray = "=SUM(IF(YourDataSheetName!Cells(1,1):YourDataSheetName!Cells(10,1)>5, YourDataSheetName!Cells(1,1):YourDataSheetName!Cells(10,1), 0))"
   

   Note: This works, but using a worksheet object is cleaner and easier to maintain, especially in loops where your range changes.

Key Takeaway #

Always qualify your cell/range references with a worksheet object when working across sheets—this eliminates the ambiguity that breaks FormulaArray. R1C1 works because it handles relative context automatically, but A1-style can work just as well once you add that explicit sheet reference.

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