VBA Count occurrence of an element in a multi-dimensional array, how?

I have defined a multi-dimensional array using range in vba, for example

Dim Arr() as Variant
Arr = Range("A1:F5")


This resulted the a 5x6 array Arr(1,1) to Arr(5,6)

I want to count the occurrence of a string, say "ABC" in Arr(5) (i.e. Row 5) only.

The following code can find the count of "ABC" in the all of the array

For Each x in Arr
if x = "ABC" then Cnt = Cnt + 1
Next


But if only want to count dimension 5, Arr(5) return an error.

2 Answers
2

Sub FindABC()
Dim Arr(), cnt, x
Const ROW_NUM = 5
Arr = Range("A1:F5")
For x = 1 To UBound(Arr, 2)
cnt = cnt + IIf(Arr(ROW_NUM, x) = "ABC", 1, 0)
Next
MsgBox cnt
End Sub


Use Index to peel off the last 'row' from the second rank. Splitting the Joined result will return a case-sensitive COUNTIF result.

Dim Arr() As Variant, tmp As String, d As String, cs As Boolean

Arr = Range("A1:F5").Value2

d = ChrW(8203)
cs = False

tmp = d & Join(Application.Index(Arr, 5, 0), d) & d

If cs Then
Debug.Print UBound(Split(tmp, "ABC"))
Else
Debug.Print UBound(Split(LCase(tmp), LCase("ABC")))
End If


A non-case-sensitive result can be returned by converting both the joined array and the criteria to a common case.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.