如何使用VBA使用数组公式填充Excel工作表中的范围？

Actually the input range is bigger than the actual range required for the array formula as well. So it would be nice if the answer also includes the code to resize the range before filling in with array formula.

实际上,输入范围也大于阵列公式所需的实际范围。因此,如果答案还包括在填充数组公式之前调整范围大小的代码,那将是很好的。

2 个解决方案

#1

This seems to work for me

这似乎对我有用

Call rng.Clear
Dim rngState As Range
Set rngState = rng.Resize(nRowCount, nColumnCount)
rngState.FormulaArray = "whatever_array_formula"
rngState.Calculate

#2

What I was looking for but just my more thorough version given that the array has already been populated:

我正在寻找但只是我的更彻底的版本,因为该阵列已经填充:

Sub PasteArray(vTheArray As Variant)
Dim rPasteHere As Range

With ActiveWorkbook
    Set rPasteHere = .Sheets("PayRoll").Range("A1").CurrentRegion  'Assign the region to use
    With rPasteHere
        .Clear  'Wipe the current region clean
        Set rPasteHere = .Resize(UBound(vTheArray, 1) + 1, UBound(vTheArray, 2) + 1)  'Resize the region to your input
        .FormulaArray = vTheArray  'Dump the array into the resized region
    End With
End With
Set rPasteHere = Nothing  'Clean up!
End Sub

Remember that arrays are zero based, thus the +1 in the .Resize function. For my application I hard-coded the sheet name and range so naturally how rPasteHere comes to be is up to the individual.

请记住,数组是基于零的,因此.Resize函数中的+1。对于我的应用程序,我对工作表名称和范围进行了硬编码,因此rPasteHere自然是如何取决于个人的。

#1