If your UBound values are not -1, then the array has managed to find the value that you were searching for. The second array will hold the values found by the filter. If UBound(strSubNames ) > -1 Then MsgBox ('I found Bob') 'if you UBound value is greater than -1, then the value has been found 'declare a variant to store the filter data in StrName() = Array('Bob Smith', 'John Davies', 'Fred Jones', 'Steve Jenkins', 'Bob Williams') Find values that match the Filter Sub FindBob() If these are not included they are set to True and vbCompareBinary respectively. The Source Array and the Match as String are required while the Include as Boolean and the Compare as vbCompareMethod are optional. The Syntax of the Filter option is a followsįilter(Source Array, Match as String,, ) Z = Filter(Array, String, True, vbCompareBinary) To search for a value in a one-dimensional array, you can use the Filter Function. There are a number of ways you can search for a string in an array – depending on whether the array is a one dimensional or multi-dimensional. This tutorial will demonstrate how to Search for (Find) a Value in an Array in VBA Using a Loop to Search through an array.Find values that DO NOT match the Filter.

Visual basic for excel array