VBA Arrays - Check strict (not approximative) match

Question

If UBound(Filter(myArray, Sheets(i).Cells(1, j).Value, True)) = -1 Then
 'take action
End if

I used this syntax to compare an element found in Cells(1, j) (e.g. "ally") to all the elements of an array (e.g. "mally", "kate", "becks"), and to take action when no exact match is found. Trouble is, based on this line of code it seems "ally" is considered as matching "mally" (probably because "ally" is a substring from "mally"), whereas I want "ally" to be recognised as distinct from "mally".

Any help with the syntax as to achieve this? Thank you!

Does this answer your question? [Check if a value is in an array or not with Excel VBA](https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba) — questionto42, Jun 12 '21 at 18:49

score 11 · Answer 1 · answered Jan 09 '15 at 12:49

11

IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))

answered Jan 09 '15 at 12:49

Sandeep Bhat

315
5
13

score 3 · Accepted Answer · answered Jan 07 '13 at 23:46

Filter will return any items that partially match. The work around suggested by Microsoft is to then search the filtered array for exact matches.

Function FilterExactMatch(astrItems() As String, _
                          strSearch As String) As String()

   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long

   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)

   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)

   ' Resize temporary array to be same size.
   ReDim astrTemp(lngLower To lngUpper)

   ' Loop through each element in filtered array.
   For lngIndex = lngLower To lngUpper
      ' Check that element matches search string exactly.
      If astrFilter(lngIndex) = strSearch Then
         ' Store elements that match exactly in another array.
         astrTemp(lngCount) = strSearch
         lngCount = lngCount + 1
      End If
   Next lngIndex

   ' Resize array containing exact matches.
   ReDim Preserve astrTemp(lngLower To lngCount - 1)

   ' Return array containing exact matches.
   FilterExactMatch = astrTemp
End Function

This code is taken from http://msdn.microsoft.com/en-us/library/office/aa164525%28v=office.10%29.aspx

score 3 · Answer 3 · answered Jan 08 '13 at 23:01

If the array is only used for this comparison and not needed for anything else you could also force full word comparisons by adding your own delimiters that never appear in the data - maybe square brackets.
So if you change your array to contain "[mally]", "[kate]", "[becks]"
then your condition becomes:

If UBound(Filter(myArray, "[" & Sheets(i).Cells(1, j).Value & "]", True)) = -1

score 2 · Answer 4 · answered Jan 07 '13 at 23:54

If you do not need to use Filter then the below snippet would work

Dim v
Dim bMatch As Boolean
bMatch = False

For Each v In myArray
    'compare strings
    If StrComp(CStr(v), Sheets(i).Cells(1, j).Value, vbTextCompare) = 0 Then
        bMatch = True
    End If
Next

If Not bMatch Then
'do something
End If

VBA Arrays - Check strict (not approximative) match

4 Answers4

Linked

Related