How to run a formula in a large range of cells with VBA

[gt213]

I have a worksheet that populates with some amount of dates in col A and account codes in row 1. I then have a range with is from B3Last Col, Last Row) called MyRange.

I now want to perform an index match array formula in each cell in my range. (The results of the index match can be just the value result, I don't need the formula in the cell if it will slow down the process.

Currently I have found the following, this does work however it takes far too long to process. I really want it to be fully automated as the workbook will be shared and used by others.

Sub setrange()
Dim MyRange As Range
Dim i As Integer
Dim j As Integer
i = Application.WorksheetFunction.CountA(Rows(1).EntireRow.Cells) + 1
j = Application.WorksheetFunction.Count(Columns(1).EntireColumn.Cells) + 2
Set MyRange = Range(Cells(3, 2), Cells(j, i))

Const pullFormula = "=INDEX(Returns!$M:$M,MATCH(Sheet1!B$1&Sheet1!$A3,Returns!$C:$C&Returns!$E:$E,0))/100"

With Sheet1
With MyRange
.Formula = pullFormula
.FormulaArray = .FormulaR1C1
End With
End With

End Sub

 

[GBGPneus]

I don't know if this is what you're looking for at all, but I think I accomplished something similar with this formula I used in my Worksheet:

Sub AddFormula()
    
    Dim Lastrow As Long


    Application.ScreenUpdating = False


    Lastrow = Range("H" & Rows.Count).End(xlUp).Row
    Range("J1:J" & Lastrow).Formula = "= """" & A1 & "" "" & B1 & "" "" & C1 & "" "" & I1 & "" "" & F1 & "" "" & G1 & """""
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True


End Sub

In my case, I wanted to prepare a table. But I'm pretty sure this works if you want to apply a formula to a range of cells.
What it does is: it checks Column H's length and fills column J accordingly to column H.

Hope this does anything for you!

 

[gt213]

Thanks GBGP, that doesn't help with what I'm having trouble with. I can apply the formula to my range of cells, the issue is with how long it takes to run. I'm hoping for a solution which doesn't require putting the formula into each cell.