Increase element value in vba array

Question

I work in VBA for Excel, and I have an array of integers. What I'm trying to do is add 1 to a specific element inside the array when my conditions are met. In my code example, if the cell in column A is 5, I would like to add 1 to the 5th position of the array, or if the cell is 3, then add 1 to the third position of the array.

Sub CountCustomers()
    Dim myArray(5)
    Dim i as integer
    For i = 1 to 10
        If Cells(i,1) = 5 Then
           myArray(4) = myArray(4)+1
        ElseIf Cells(i,1) = 3 Then
           myArray(2) = myArray(2)+1
        End If
    Next
End Sub

When I run it like this, it ends with 1 in the correct position, but will not increase the values ​​above. Is there any way to do this?

Answer 1

A few options below.

• Better to work with arrays for speed than loops (option 2)
• CountIF ( 1)

, Select , Else If

code # 1

Sub Option1()
Debug.Print "number of 5's: " & Application.WorksheetFunction.CountIf([a1:a10], 5)
Debug.Print "number of 3's: "; Application.WorksheetFunction.CountIf([a1:a10], 3)
End Sub

code # 2

Sub Option2()
Dim x
Dim myArray(1 To 5)
Dim lngCnt As Long

'put range into 2D variant array
x = [a1:a10].Value2
For lngCnt = 1 To UBound(x)
    Select Case x(lngCnt, 1)
    Case 5
    myArray(4) = myArray(4) + 1
    Case 3
    myArray(2) = myArray(2) + 1
    Case Else
    'do nothing
    End Select
Next

Debug.Print myArray(4), myArray(2)

End Sub

Answer 2

, . : Excel VBA 3 - , ( )

. myArray(2) myArray(4) .