VBA Countif using Arrays as arguments

Question

I was wondering if there is a possibility to use countif on arrays.

Currently there are two arrays. One is the Array with the Range (RangeArray) and the other the Criteria array (CritArray) which comes from another workbookbut is saved in an array. I'm trying to use the countif method in VBA using arrays if and store the countif values in a cell. So I don't need to loop between workbooks all the time.

Dim RangeArray, CritArray as Variant

RangeArray = Array(1,2,3,4,2,4,2,5,7,1,7,1,2)
CritArray = Array(1,2)

For i = 1 To LastRow
    Cells(i, 1).Value = WorksheetFunction.CountIf(RangeArray, CriteriaArray)
Next i

When I try to do something amongst these lines it keeps giving the error "object required".

Any help would be kindly appreciated!

Kind regards,

What should the result be given the two arrays in your question? — BigBen
– BigBen, Commented Mar 6, 2020 at 14:18

Naresh · Accepted Answer · 2020-03-06 14:46:47Z

1

Sub test()
Dim RangeArray, CritArray As Variant
Dim Counts As New Collection

RangeArray = Array(1, 2, 3, 4, 2, 4, 2, 5, 7, 1, 7, 1, 2, 11)
CritArray = Array(1, 2)

For i = 0 To UBound(CritArray)
Count = 0
    For j = 0 To UBound(RangeArray)
    If CritArray(i) = RangeArray(j) Then
    Count = Count + 1
    End If
    Next
Counts.Add Count
Next

For k = 1 To Counts.Count
Cells(k, 1) = Counts(k)
Next
End Sub

answered Mar 6, 2020 at 14:46

Naresh

3,0372 gold badges11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Countif using Arrays as arguments

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related