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