CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

sumif function vba faster

I am using sumifs function in my vba codes but I noticed the speed has been slow, takes minutes since I have 10k-20k records each week.

Here is the portion of codes that reflect the sumif funciton, so the codes work but I am looking for ways that can help it to run faster. 

For i = 2 To LR10
MOB_T.Cells(i, 13).Value = Application.WorksheetFunction.SumIfs(Rng370, Rng380, MOB_T.Cells(i, 12).Value, Rng390, Selection.Cells(6, 2).Value)
Next i

Upvotes: 0

Views: 2396

Answers (2)

kulapo
kulapo

Reputation: 397

You can try to set the Calculation to manual before running your code.

Application.Calculation = xlCalculationManual

Then once the loop is done, turn it on again:

Calculate
Application.Calculation = xlAutomatic

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

Avoid looping. The slow part is transferring data between VBA and the spreadsheet. Enter the formula at once for the whole target range, then convert it to static value if necessary. Something like: 

range("M2:M" & LR10).Formula = "=SUMIFS(some params here)"

You could also consider using FormulaR1C1 which might make your formula much easier and readable.

To convert to static value - if required - just use:

With range("M2:M" & LR10)
   .value = .value
End With

Upvotes: 2

Related Questions