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

 

[BrianM45]

Hi Cakz Primz

It all depends on the PC you have and the number of lines
I don't think we can do anything for you

Regards

 

[kevin9999]

Hi Prima, could you provide a sample of your data using the XL2BB add in?

 

[James006]

Hi,
There are several potential solutions ...
The simplest is to use Evaluate

.Range("C2") = Evaluate("=VALUE(TEXTAFTER(B2,""_""))")

 

[Cakz Primz]

Hi Cakz Primz

It all depends on the PC you have and the number of lines
I don't think we can do anything for you

Regards

Dear BrianM45,

Thank you for your kind attention.

Best regards,
prima - Indonesia

 

[Cakz Primz]

Hi Prima, could you provide a sample of your data using the XL2BB add in?

Dear kevin9999,

Nice to see you again, hope you can help me again.
I put the sample of the file as below, with only 73KB. Only the number of rows are different then the original one.

In the origin file, number of rows:
- sheet ZJMU = 114,152 rows
- sheet MRR = 448,057 rows
- sheet Checker = 4,649 rows (where formulas running)

Link of sample file (73KB):
ZJMU_2020.xlsb

Link of original file (17,753KB):

ZJMU_2020_Original.xlsb

drive.google.com

XL2BB:

ZJMU_2020.xlsb
	L
8
Checker

Thank you so much for your kind attention, effort and help.

Best regards,
prima - Indonesia

 

[Cakz Primz]

Hi,
There are several potential solutions ...
The simplest is to use Evaluate

.Range("C2") = Evaluate("=VALUE(TEXTAFTER(B2,""_""))")

Dear James006:

Do I need to apply Evaluate for 6 functions?

Thank you for kind attention.

Best regards,
prima - Indonesia

 

[offthelip]

The really fast way of coding this is not to use formula at all but to do the entire calculation in VBA using techniques such as dictionary which can be used to find unique values, loops through an array to filter and sum values. Unfortunately I can't really recode this for you because my version of EXCEL doesn't have the filter function so I don't know how to use it or what you are doing.
Doing this sort of calculation in memory is usually at least 1000 times faster so I would expect it to be under 1 second. Perhaps if you explained in words what you are trying to do I could help you

 

[Cakz Primz]

The really fast way fo coding this is not to use formula at but to do the entire calculation in VBA using techniques such as dictionary which can be used to find unique values, loops through an array to filter and sum values. Unfortunately I can't really recode this for you becuase my version of EXCEL doesn't have the filter functioin so I don't know how to use it. Doing this sort of calculation in memeory is usually at least 1000 times faster so I would expect it to be under 1 second

Dear offthelip,

Thank you for your kind attention and suggestion. But my VBA skill is quite poor, so I don't know to write a looping procedure.
But I will try to find in Google/ChatGPT.
By the way, I am using Microsoft Office 365.

Again, thank you very much.

Best regards,
prima - Indonesia

 

[kevin9999]

Dear kevin9999,

Nice to see you again, hope you can help me again.
I put the sample of the file as below, with only 73KB. Only the number of rows are different then the original one.

In the origin file, number of rows:
- sheet ZJMU = 114,152 rows
- sheet MRR = 448,057 rows
- sheet Checker = 4,649 rows (where formulas running)

Link of sample file (73KB):
ZJMU_2020.xlsb

Link of original file (17,753KB):

ZJMU_2020_Original.xlsb

drive.google.com

XL2BB:

ZJMU_2020.xlsb
	L
8
Checker

Thank you so much for your kind attention, effort and help.

Best regards,
prima - Indonesia

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