☰

Excel 2010 Range.FormulaArray 使用 Range.Replace

tag-icon • tag-icon microsoft-excel-2010 vba
Excel 2010 Range.FormulaArray 使用 Range.Replace

我正在尝试自动化一些日常工作，其中我使用 INDEX()+MATCH() 数组公式从不同的工作簿中抓取一些数据放入一个“主”工作簿中。手动操作可以按预期工作，但现在我想自动化这个过程。

我已经达到了 Range.FormulaArray 属性的 255 个字符的限制，并试图使用提供的技巧来规避它这里，但没有运气。

运行此代码时，公式的 p1 部分被插入到范围中，但 2 个 .Replace 未被执​​行。

我尝试了 .Replace 函数的“What”部分的不同变体，“X_X_X()”和“{0}”以及许多其他变体，但都没有成功。

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim p1, p2, p3 As String
p1 = "=IF(G2<>"""",INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P,MATCH(1,({0})*({1}),0)),"""")"
p2 = "B2='C:\path\to\[document.xlsx]Sheet1'!$B:$B"
p3 = "C2='C:\path\to\[document.xlsx]Sheet1'!$C:$C"

With ws.Range("R2")
    .FormulaArray = "=IF(G2<>"""",INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P,MATCH(1,({0})*({1}),0)),"""")"
    .Replace "{0}", p2
    .Replace "{1}", p3
End With

我手动粘贴的公式是：

=IF(G2<>"";INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P;MATCH(1;(B2='C:\path\to\[document.xlsx]Sheet1'!$B:$B)*(C2='C:\path\to\[document.xlsx]Sheet1'!$C:$C);0));"")

答案1

我确实设法通过首先在“主”工作簿中定义外部工作簿中的范围名称来解决这个问题。通过这样做，我将公式保持在 .FormulaArray 的 255 个字符限制以下。一夜好眠和对问题的全新审视就足够了 :-)

答案2

考虑首先正常输入公式，然后将其转换为数组公式，就像在工作表中一样：

Sub qwerty()
    ActiveCell.Formula = "=MAX(IF(A2>B1:B12,B1:B12))"
    With Application
        .SendKeys "{F2}" ' get into edit mode
        .SendKeys "^+~" '  use CNTRL + Shift = Enter
    End With
End Sub

编辑＃1：

此宏应从Excel窗口，而不是维基百科窗户。

相关内容