Need help: VBA is too slow to write 6 formulas

[Cakz Primz]

Dear all,

I have a code below to write 6 formulas and change the formula into values. Without any other workbook open, it takes more than 600 seconds to execute the code.
Is there any solution to speed it up?

Sub ExtractPONumber_MSRLineItem()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Dim t As Double: t = Timer
    
    Workbooks.Open fileName:="P:\12.COMMON_FOLDER\PRIMA\MSR.xlsb"
    Windows("ZJMU_2020.xlsb").Activate
    Sheet3.Activate
    
    Dim ws As Worksheet
    Dim lRow As Long
    lRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    Set ws = ActiveSheet
    
    With ws
    .Range("C2:C" & lRow).FormulaR1C1 = "=VALUE(TEXTAFTER(RC[-1],""_""))"
    .Range("D2:D" & lRow).FormulaR1C1 = "=SUM(--(LEN(UNIQUE(FILTER([MSR.xlsb]DataSource!R4C12:R150000C12,[MSR.xlsb]DataSource!R4C42:R150000C42=RC[-2],"""")))>0))"
    .Range("E2:E" & lRow).FormulaR1C1 = "=SUM(--(LEN(UNIQUE(FILTER(MRR!R3C11:R600000C11,MRR!R3C2:R600000C2=RC[-2],"""")))>0))"
    .Range("F2:F" & lRow).FormulaR1C1 = "=SUM(--(LEN(UNIQUE(FILTER(MRR!R3C11:R600000C11,MRR!R3C2:R600000C2=RC[-3],"""")))>0))"
    .Range("G2:G" & lRow).FormulaR1C1 = "=XLOOKUP(RC[-4],ZJMU!C[9],ZJMU!C[11])"
    .Range("H2:H" & lRow).FormulaR1C1 = "=IF(AND(RC[-4]>0,RC[-2]=0),0,1)"
    .Range("C2:H" & lRow).Value = .Range("C2:H" & lRow).Value
    End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

    Application.DisplayAlerts = False
    Workbooks("MSR.xlsb").Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    MsgBox "Finish in " & Timer - t & " seconds", , "Luce"

End Sub

Thank you very much in advanced.
Best regards,
Prima

 

[Cakz Primz]

Thanks Prima,
I'll also need the file MSR.xlsb...

Dear kevin9999,

I will upload it, and inform you when it's done.

Thank you very much,
prima - Indonesia

 

[RoryA]

There is no point in turning calculation off and then back on with that code.

 

[Cakz Primz]

Dear kevin9999,

I will upload it, and inform you when it's done.

Thank you very much,
prima - Indonesia

Dear kevin

Below is the link for MSR.xlsb file (5,276KB)
MSR.xlsb

Thank you,
prima - Indonesia

 

[Cakz Primz]

There is no point in turning calculation off and then back on with that code.

Dear RoryA,

So what should I do?

Thank you so much for your kind attention.

Best regards,
prima - Indonesia

 

[snjpverma]

There is no point in turning calculation off and then back on with that code.

Could you please explain why?

 

[RoryA]

Because if you use .Range("C2:C" & lRow).FormulaR1C1 the formulas have to be calculated to populate the cells with something (otherwise this code wouldn’t work at all since they are replaced with values before turning calculation back on). It is most likely slowing the code down changing the calculation mode.

 

[snjpverma]

Because if you use .Range("C2:C" & lRow).FormulaR1C1 the formulas have to be calculated to populate the cells with something (otherwise this code wouldn’t work at all since they are replaced with values before turning calculation back on). It is most likely slowing the code down changing the calculation mode.

Ok. Thank you for the information. I wonder how OP has been using it then.