I have a proces that involves a sumifs function with multiple arguments applied on a large range for many cells. The end product should involve cells containing only values. An excel user would:

1. Type the formula.
2. Copy this to all cells in the table.
3. Copy all cell and replace with only values. (1st method below)

I usually like to avoid such visible intermediate steps in the spreadsheet in vba by copying all cells to a variant, doing the calculations (making use of Excel's own functions 'application.worksheetfunction'), and pasting the variant back to the range (2nd method below). Using such method, there's less chance of user interference during the calculations and it is usually quite efficient as well. However, I do not seem to be able to beat the performance of emulating the Excel user method using variants. In fact, on a large scale it gets much slower.

`

Sub countif_2methods()
    Range("A1") = "Marty"
    Range("A2") = "Marty"
    Range("A3") = "Emmet"
    Range("A4") = "Emmet"
    Range("A5") = "Biff"
    
    '1st method
    With Range("B1")
        .Formula2R1C1 = "=COUNTIF(C1,R[]C1)"
        .AutoFill Destination:=Range("B1:B5")
    End With
    With Range("B1:B5")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    
    '2nd method
    Dim v As Variant
    v = Range("B1:B5")
    Dim i As Long
    For i = LBound(v, 1) To UBound(v, 1)
        v(i, 1) = Application.WorksheetFunction.countif(Range("A:A"), Range("A" & i))
    Next i
    Range("B1:B5") = v
    
End Sub

`

Is there some way to apply the application.worksheetfunction directly on the entire destination range in a manner at least as efficient as "writing_formula -> copy to other cells -> copy and paste as values" (1st method)?

I tried the 2 methods described on a large scale. For a searchrange of 35k cells, destination range of 3k cells using a sumifs function with 6 arguments, the 1st method was 10 times as fast as doing the calculations inside the variant.

CodePudding user response：

Applying the formula to the entire range in one operation and assigning the ranges value to itself yields the best results using a formula. But don't forget to optimize the performance by turning off Application.ScreenUpdating and setting Application.Calculation to xlCalculationManual.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'3rd method
With Range("B1").Resize(5)
    .Formula2R1C1 = "=COUNTIF(C1,R[]C1)"
    .Value = .Value
End With

Application.Calculation = xlCalculationAutomatic

The fastest way to do the count-ifs would be use a dictionary to count the matches and fill an array to write back to the worksheet.

Sub DictionaryMatch()
    Dim Map As Object
    Set Map = CreateObject("Scripting.Dictionary")
    
    Dim Data As Variant
    Dim Result As Variant
    

    '3rd method
    With Range("A1").CurrentRegion
        Data = .Columns(1).Value
        Result = .Columns(2).Value
    End With
    
    Dim Key As String
    Dim r As Long
    
    For r = 1 To UBound(Data)
        Key = Data(r, 1)
        Map(Key) = Map(Key)   1
    Next
    
    For r = 1 To UBound(Data)
        Key = Data(r, 1)
        Result(r, 1) = Map(Key)
    Next
       
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With Range("A1").CurrentRegion
         .Columns(2).Value = Result
    End With
    
    Application.Calculation = xlCalculationAutomatic
  
    
End Sub

CodePudding user response：

No guarantee it will be faster, but you can do it directly like this:

Range("B1:B5").Value = Application.countif(Range("A:A"), Range("A1:A5"))