CODEWITHSUNDEEP
vba
user2300403
user2300403

Reputation: 309

FILTER Function for integers - VBA

I searched the website but was not succesfful and tried doing some research on this but facing with " Type Mismatch" error.

I declared an array as integer type but the FILTER function seems to work only with STRING's. Can you please let me know how I can use the FILTER function for integers?

If UBound(Filter(CntArr(), count)) > 0 Then msgbox "found"

End If

Upvotes: 1

Views: 2109

Answers (2)

FFFffff
FFFffff

Reputation: 1045

Below I have created IsIntegerInArray() function that returns boolean. Follow the two Subs for an example of integer array declaration. Declaring array as Integer should also prevent some unnecessary bugs caused by implicit data conversion.

Sub test_int_array()
    Dim a() As Integer

    ReDim a(3)
    a(0) = 2
    a(1) = 15
    a(2) = 16
    a(3) = 8

    '''   expected result: 1 row for each integer in the array
    Call test_printing_array(a)
End Sub

Sub test_printing_array(arr() As Integer)
    Dim i As Integer

    For i = 1 To 20
        If IsIntegerInArray(i, arr) Then
            Debug.Print i & " is in array."
        End If
    Next i
End Sub

Function IsIntegerInArray(integerToBeFound As Integer, arr() As Integer) As Boolean
    Dim i As Integer

    '''   incorrect approach:
    '''   IsIntegerInArray = (UBound(Filter(arr, integerToBeFound)) > -1)   ' this approach searches for string, e.g. it matches "1" in "12"

    '''   correct approach:
    IsIntegerInArray = False
    For i = LBound(arr) To UBound(arr)
        If arr(i) = integerToBeFound Then
            IsIntegerInArray = True
            Exit Function
        End If
    Next i
End Function

Upvotes: 1

shibormot
shibormot

Reputation: 1638

as i understand you need to know if specified count present in array. You can use for loop for it:

Dim found as Boolean
found = False
For i = 0 To UBound (CntArr())
   If CntArr(i) = count Then
      found = True
      Exit For
   End If
Next i
If found Then msgbox "found" End If

Upvotes: 1

Related Questions