I am using the
sumifs function in my VBA code but I noticed the speed has been slow. It takes minutes since I have 100k-200k records each week.
I am using the
sumifs function to find how many records there are for each Cov A which is in a dollar amount. I also want to match the criteria in selection sheet so I am cross-matching multiple sheets. Values in column M is based on values in column L so I used R1C1 to reference that. I tried to add
Application.Calculation = xlCalculationManual or
Application.ScreenUpdating=False but speed does not improve as much as I anticipated.
I also tried to use Range(s) instead of R1C1 but speed is about the same.
Here is the portion of codes that reflect the sumif function, so the code works just fine but I am looking for ways in which can improve the speed.
Sub test_sumifs() Dim MOB_T, Selection, Inforce As Worksheet Dim Rng370, Rng380 As Range Dim LR10 As Long 'Avg Cov A Inforce Set MOB_T = Worksheets("MOB_T") Set Selection = Worksheets("Selection") Set Inforce = Worksheets("Inforce") Inforce.Columns("AD").Copy MOB_T.Columns("L").PasteSpecial xlPasteValues MOB_T.Range("L1").ClearContents ActiveSheet.Range(Range("L2"), Range("L2").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo LR10 = MOB_T.Cells(Rows.Count, "L").End(xlUp).Row With MOB_T.Range("M2:M" & LR10) .FormulaR1C1 = "=SUMIFS(Inforce!C75,Inforce!C30,MOB_T!RC[-1],Inforce!C61,Selection!R6C2)" .Value = .Value End With Set Rng370 = MOB_T.Range("L2:L" & LR10) Set Rng380 = MOB_T.Range("M2:M" & LR10) MOB_T.Cells(2, 14).Value = Application.WorksheetFunction.SumProduct(Rng370, Rng380) MOB_T.Cells(3, 14).Value = "=SUM(M2:M" & LR10 & ")" MOB_T.Cells(4, 14).Value = MOB_T.Cells(2, 14).Value / MOB_T.Cells(3, 14).Value MOB_T.Range("E15") = MOB_T.Range("N4") MOB_T.Columns("M:N").ClearContents End Sub