CODEWITHSUNDEEP
arraysvbaexcel
jw0ng
jw0ng

Reputation: 91

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.

Upvotes: 1

Views: 3423

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

JohnyL
JohnyL

Reputation: 7142

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

Upvotes: 2

Related Questions