# Checking array using function

#### MM91

##### Board Regular
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

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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:

VBA Code:
``````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``````

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

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

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

VBA Code:
``````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
End If

End Sub``````

I have no idea what your loop is meant to do?
Code:
``````For i = 1 To LastRowSPtbl
...``````

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!

Replies
3
Views
363
Replies
5
Views
902
Replies
7
Views
380
Replies
3
Views
344
Replies
7
Views
156

1,203,172
Messages
6,053,888
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back