Checking array using function

[MM91]

Hi I am trying to check an array to get a match and if the isnt a match add into array name filters(). I keeps giving me a syntax error. Please help thank you!

Dim PartAbbvArray() as string

Sub AddFilter()

For i =1 to 10
PartabbvArray(i)="part" & i
Next

Dim i As Integer


IsinArray(FilterCheck,PartAbbvArray(),false)

If IsInArray = False Then
For i = 1 To NumberOfFilters
Filters(i) = FilterClicky
Next i



End Sub


Public Function IsInArray(Arr As Variant, ValueToFind As Variant, Optional MatchCase As Boolean) As Boolean
IsInArray = InStr(1, Chr(1) & Join(Arr, Chr(1)) & Chr(1), Chr(1) & ValueToFind & Chr(1), Abs(MatchCase))
End Function

 

[StephenCrump]

You need to reference the result of a function in a statement. You can't just have a line that says:
IsinArray(FilterCheck,PartAbbvArray(),false)

Your function is expecting two to three arguments, so you can't say:
If IsInArray .... without specifying two to three arguments.

From the code you've posted, we have no idea what NumberOfFilters, Filters() and FilterClicky might be.

Here's some code that uses your function. I hope it helps:

Sub AddFilter()

    Dim i As Long
    Dim PartAbbvArray() As String
    Dim Arr As Variant
    Const N = 10
    ReDim PartAbbvArray(1 To N)
    Arr = Array("Hello", "Part1", "Hello", "Part9", "Hello")
    
    For i = 1 To N
        PartAbbvArray(i) = "part" & i
        If IsInArray(Arr, PartAbbvArray(i)) Then _
            MsgBox PartAbbvArray(i) & " found!"
    Next i

End Sub
Public Function IsInArray(Arr As Variant, ValueToFind As String, Optional CompareOption As Long = vbTextCompare) As Boolean
    IsInArray = InStr(1, Chr(1) & Join(Arr, Chr(1)) & Chr(1), Chr(1) & ValueToFind & Chr(1), CompareOption)
End Function

 

[MM91]

Thank you so much for the reply it wasvery helpful! It feels like I am closer however I have tried to mnodify for my use but i keep getting type mismatch. apologies I had public declarations i now included below

Public Filters() As Variant
Public FilterClicky As String
Public PartAbbvArray() As Variant

Sub AddFilter()

FilterClicky = "test"
Dim i As Long
const n = 10
redim PartAbbvArray (1 to n)

PartAbbvArray()=("test","part1","test2","Part2","test3","part3","test4","part4", "test5","part5")


For i = 1 To LastRowSPtbl
If IsInArray(PartAbbvArray(i), FilterClicky) Then
MsgBox PartAbbvArray(i) & " found!"
Else
MsgBox "not found"
End If
Next i

End Sub

Public Function IsInArray(arr As Variant, ValueToFind As String, Optional CompareOption As Long = vbTextCompare) As Boolean
IsInArray = InStr(1, Chr(1) & Join(arr, Chr(1)) & Chr(1), Chr(1) & ValueToFind & Chr(1), CompareOption)
End Function

 

[StephenCrump]

If you're trying to find whether FilterClicky is contained within PartAbbvArray, then:

Sub AddFilter()

    Dim FilterClicky As String
    Dim PartAbbvArray As Variant

    FilterClicky = "test"
    PartAbbvArray = Array("test", "part1", "test2", "Part2", "test3", "part3", "test4", "part4", "test5", "part5")
        
    If IsInArray(PartAbbvArray, FilterClicky) Then
        MsgBox FilterClicky & " found!"
    Else
        MsgBox "Not found"
    End If

End Sub

I have no idea what your loop is meant to do?

For i = 1 To LastRowSPtbl
...

 

[MM91]

Thank you so much, after hours of frustration I found out my problem was I was adding parenthesis to this statement If IsInArray(PartAbbvArray(), FilterClicky) instead of If IsInArray(PartAbbvArray, FilterClicky). thank you for the help!