Easy copy of an Array Formula with Dynamic Value...
 

Hello -- I am sorry, this is a re-post on a question posed earlier. The
solution given worked in part. I have an array formula - {with the
shift+ctrl+enter}:

{=INDEX('Sheet2'!$A$8:$AI$13219,MATCH($G6205&"",'s heet2'!$A$8:$A$13219&'sheet2'!$B$8:$B$13219,0),30) }

There are four columns with the same formula except a different number - 27,
29, and 34.

I would like to copy the formula throughout my spreadsheet (+10K rows). The
fill takes a long time and hangs.

Sharad Naik provided the following code...

Sub OrTryThis()
Dim i As Integer, strFormula As String
For i = 2 To 9
With Sheet1
.Range(.Cells(4, i), .Cells(6, i)).Formula = _
.Cells(2, i).Formula
strFormula = .Cells(6, i).Formula
.Range(.Cells(4, i), .Cells(6, i)).Formula = strFormula
End With
Next i
End Sub

This does not work with arrays... He suggested modifying it to the
following...

Sub ThenTryThis()
Dim i As Integer, strFormula As String
For i = 2 To 9
With Sheet1
.Range(.Cells(4, i), .Cells(6, i)).FormulaArray = _
.Cells(2, i).FormulaArray
strFormula = .Cells(6, i).FormulaArray
.Range(.Cells(4, i), .Cells(11789, i)).FormulaArray = strFormula
End With
Next i
End Sub

This just copy the value in the initial cell throughout losing the dynamic
cell reference.

Borrowed code found in the group to change a formula to an array formula but
it is also taking time.

Cells(r,c).FormulaArray = Cells(r,c).Formula

Help...

I am sorry for being so long winded...

Thanks
Dennis G.

Any and all help would be appreciated.