YasserKhalil
08-27-2017, 01:12 PM
Hello everyone
I have devised this code that countifs using arrays ..
Sub COUNTIF_Using_Arrays()
Dim ws As Worksheet
Dim a As Variant
Dim b As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
a = ws.Range("C3:IP2003").Value
b = ws.Range("IS3:IT2003").Value
For i = 1 To UBound(b, 1)
If b(i, 1) <> "" And Not IsEmpty(b(i, 1)) Then b(i, 2) = CountInArray(a, b(i, 1))
Next i
Application.ScreenUpdating = False
Application.Calculation = xlManual
ws.Range("IS3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Function CountInArray(ByVal arr As Variant, ByVal vMatch As Variant)
Dim v As Variant
For Each v In arr
If v = vMatch Then CountInArray = CountInArray + 1
Next v
End Function
But it took some time although I used arrays ..
Of course using COUNTIFS built-in function make the file very slow so I am searching for alternative solution ..
Any ideas how to make it faster please
Thanks advanced for any help
I have devised this code that countifs using arrays ..
Sub COUNTIF_Using_Arrays()
Dim ws As Worksheet
Dim a As Variant
Dim b As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
a = ws.Range("C3:IP2003").Value
b = ws.Range("IS3:IT2003").Value
For i = 1 To UBound(b, 1)
If b(i, 1) <> "" And Not IsEmpty(b(i, 1)) Then b(i, 2) = CountInArray(a, b(i, 1))
Next i
Application.ScreenUpdating = False
Application.Calculation = xlManual
ws.Range("IS3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Function CountInArray(ByVal arr As Variant, ByVal vMatch As Variant)
Dim v As Variant
For Each v In arr
If v = vMatch Then CountInArray = CountInArray + 1
Next v
End Function
But it took some time although I used arrays ..
Of course using COUNTIFS built-in function make the file very slow so I am searching for alternative solution ..
Any ideas how to make it faster please
Thanks advanced for any help