What is the most efficient way to fill a formula down/over and then replace with the value?

[nayr1482]

I am working on some code and part of the routine requires me to vlookup using the values on the left (dyanamic but this set ~45k rows) concantendated with the values along the top (dynamic but this set ~900 columns) and bring in the corresponding value from another sheet.

I have been trying to find an efficienct way to do this...but so far the best I found found is taking ~21 minutes.

First attempt was just entering the vlookup formula into the cell and then filling down and then filling across....this brought excel to its knees.
I then tried disabling screenupdating and calculations, then re-enabling calcs after the fill and doing range.value = range.value this ran but took a very long time
few more variations...

Where I am at now is ~21minutes to run:

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ws.Cells(7, 13).FormulaR1C1 = "=IFNA(VLOOKUP(RC2& "" | "" & R6C,'2.BOM_Explode'!C6:C7,2,FALSE)*RC3,"""")"
    ws.Cells(7, 13).AutoFill Destination:=Range(ws.Cells(7, 13), ws.Cells(7, c)), Type:=xlFillDefault
    Application.Calculation = xlCalculationManual
    Range(ws.Cells(7, 13), ws.Cells(7, c)).AutoFill Destination:=Range(ws.Cells(7, 13), ws.Cells(r, c)), Type:=xlFillDefault
    Application.Calculation = xlCalculationAutomatic
    With Range(ws.Cells(7, 13), ws.Cells(r, c))
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1, 13).Select
    End With
    Application.CutCopyMode = False

Is there a more efficienct way to do this?
Thanks for your help!

 

[offthelip]

Have alook at this thread that shows how to use the dictionary object as an alternative to vlookup, it should be at least 1000 times faster i.e less than 2 seconds:
Vlookup Alternative using Dictionary