1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ I've reopened your question and tried to come up with a title that better describes the problem it's solving (feel free to edit it again if you have a better idea!) - as a VBA reviewer I have a question though: does the module say Option Explicit at the top? \$\endgroup\$ – Mathieu Guindon Aug 31 '17 at 19:07
  • \$\begingroup\$ no I am new to VBA and I actually seldom start my codes with Option Explicit not sure if that is the issue. I usually start my codes with Sub name() \$\endgroup\$ – sc1324 Aug 31 '17 at 19:09
  • 1
    \$\begingroup\$ Option Explicit doesn't speed up anything, it simply forces you to declare every single variable you're using. Without it, VBA will happily compile a typo. Consistently specifying Option Explicit at the top of every single VBA module you ever write, contributes to increase code quality, and helps avoid embarrassing bugs =) \$\endgroup\$ – Mathieu Guindon Aug 31 '17 at 19:11
  • \$\begingroup\$ Will start using it as a habit. Thank you \$\endgroup\$ – sc1324 Aug 31 '17 at 19:12
  • 2
    \$\begingroup\$ I did some research and found that an alternative way was to load the ranges into variant arrays and then write the SUMIFS in the code, not using formulas but I am not familiar with the arrays. \$\endgroup\$ – sc1324 Aug 31 '17 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.