使用数组作为参数的VBA Countif [英] VBA Countif using Arrays as arguments

问题描述

我想知道是否有可能在数组上使用countif.

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

当前有两个数组.一个是带有范围的数组(RangeArray)，另一个是来自另一个工作簿的Criteria数组(CritArray)，但保存在数组中.我试图在VBA中使用if数组使用countif方法，并将countif值存储在单元格中.因此，我不需要一直在工作簿之间循环.

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".

任何帮助将不胜感激！

亲切的问候，

推荐答案

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

这篇关于使用数组作为参数的VBA Countif的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！