问题1：“无法设置Range类的FormulaArray属性”错误 #

这个报错通常由几个常见原因引发，你可以按以下方向排查：

• 公式长度超限：旧版Excel（2019及更早）对数组公式的字符长度有严格限制（最多255字符），如果你的公式过长就会触发这个错误。解决办法可以是拆分公式逻辑，或者先通过Range.Formula设置普通公式，再调用Range.FormulaArray转换；如果条件允许，升级到支持更长公式的新版Excel会更省心。
• 目标区域不是单个单元格：FormulaArray方法仅支持给单个单元格设置数组公式，如果你选中了多单元格直接赋值，必然报错。正确流程是先给第一个单元格设置数组公式，再用AutoFill或FillDown批量填充到需要的区域。
• 引用逻辑非法：比如你公式里用到的ROW(R[-8])，如果当前单元格所在行小于第9行（需要往上偏移8行），R[-8]会指向无效行号，导致公式本身非法，进而触发属性设置失败。要确保目标单元格的位置满足偏移引用的有效性。

问题2：可变长度横线标题的匹配与填充问题 #

你当前用SEARCH(""*__________*"",...)匹配带横线的标题，但这个模式只能命中固定数量横线的内容，这就是数据变化后代码失效的核心原因。另外原公式的索引逻辑也存在小问题，我帮你调整优化：

修正后的VBA代码 #

' 先定位目标单元格（这里用选中区域的第一个单元格，你也可以指定具体位置，比如Sheet2.Range("A10")）
Dim targetCell As Range
Set targetCell = Selection.Cells(1)

' 设置数组公式，调整匹配模式并修正索引逻辑
targetCell.FormulaArray = _
    "=INDEX('Sheet1'!R1C1:R10000C2,SMALL(IF(ISNUMBER(SEARCH(""*-*"",'Sheet1'!R1C1:R10000C2)),ROW('Sheet1'!R1C1:R10000C2)-ROW('Sheet1'!R1C1)+1),ROW(R[-8])),1)"
' 批量填充到选中区域（如果需要多单元格填充）
targetCell.AutoFill Destination:=Selection, Type:=xlFillDefault

关键调整说明 #

1. 匹配模式优化：把固定横线的"*__________*"改成"*-*"，这样可以匹配包含任意数量横线的单元格，不管横线长度如何变化都能命中目标标题。如果你的标题是“前后有内容、中间是连续横线”，可以更精准地用"*---*"（匹配至少3个连续横线），避免误判单个横线的无关内容。
2. 索引逻辑修正：原公式里ROW('Sheet1'!R1C1:R10000C2)返回的是工作表绝对行号，直接作为INDEX的行参数会出错（INDEX需要的是相对于引用区域的偏移量，不是绝对行号）。改成ROW('Sheet1'!R1C1:R10000C2)-ROW('Sheet1'!R1C1)+1后，得到的是目标区域内的相对行索引，确保INDEX能正确取值。
3. 批量填充优化：先给单个单元格设置数组公式，再用AutoFill填充，避免直接给多区域设置FormulaArray导致的报错。

额外小提示 #

如果你的Excel版本支持动态数组（365/2021），完全可以不用VBA，直接在单元格输入=FILTER('Sheet1'!A1:B10000,ISNUMBER(SEARCH("-",'Sheet1'!A1:B10000)))，Excel会自动溢出填充结果，逻辑更简洁高效。另外建议先在Excel单元格手动输入数组公式（按Ctrl+Shift+Enter）验证逻辑正确，再移植到VBA中（注意把双引号转义成两个双引号）。

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