Return to VBA Code Examples

VBA Search for (Find) Value in Array

This tutorial will demonstrate how to Search for (Find) a Value in an Array in VBA

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.

Searching in a One-Dimensional Array

To search for a value in a one-dimensional array, you can use the Filter Function.

The Syntax of the Filter option is a follows

Filter(Source Array, Match as String, [Include as Boolean], [Compare as vbCompareMethod])

The Source Array and the Match as String are required while the Include as Boolean and the Compare as vbCompareMethod are optional.  If these are not included they are set to True and vbCompareBinary respectively.

Find values that match the Filter

The second array will hold the values found by the filter.  If your UBound values are not -1, then the array has managed to find the  value that you were searching for.

You can also see how many times the text appears in the original array.

Find values that DO NOT match the Filter

The [Include as Boolean] option allows you to find how many values in your array which DO NOT match your filter

we have therefore amended this line:

with this line:

Using this line in the code, would return all the names that do NOT match “Bob”.

vba find array msgbox

Case Sensitive Filters

You will find that the filter is case sensitive by default.  This is true for all VBA functions.  If you want to search for text that is not case sensitive, you need to amend your code slightly.

Adding vbTextCompare to your filter line will enable your code to find “bob” or “Bob”.  If this is omitted, VBA by default uses vbBinaryCompare which will only look for data that is an EXACT match.  Notice in the example above, we have left out the [Include as Boolean] argument so True is assumed.

Option Compare Text

Alternatively, you can add the text Option Compare Text to the top of your module – this will make all the functions that you write in that particular module case insensitive.

vba find array option compare text

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Using a Loop to Search through an array

Using a loop is a little bit more complicated than using the Filter function.  We can create a function that will loop through all the values in the array.

In order to find a part of the text string ie “Bob” instead of “Bob Smith” or “Bob Williams”, we needed to use the Instr Function in the If Statement.  This looked in the string returned by the loop from the Array to see if “Bob” was in the string, and as it was in the string, it would return a message box and then Exit the Loop.

Searching in a Multi-Dimensional Array

We also use the loop to search through a multi-dimensional array.  Once again, we need to create a function than enables us to loop through all the values in the array, but this time, we also need to loop through each dimension of the array.