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.
Add a comment
|
2 Answers
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
Comments
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.
