How to search for string in an array

by

If you want to know if the string is found in the array at all, try this function:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

As SeanC points out, this must be a 1-D array.

Example:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print IsInArray("ghi", arr)
End Sub

(Below code updated based on comment from HansUp)

If you want the index of the matching element in the array, try this:

Function IsInArray(stringToBeFound As String, arr As Variant) As Long
  Dim i As Long
  ' default return value if value not found in array
  IsInArray = -1

  For i = LBound(arr) To UBound(arr)
    If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then
      IsInArray = i
      Exit For
    End If
  Next i
End Function

This also assumes a 1-D array. Keep in mind LBound and UBound are zero-based so an index of 2 means the third element, not the second.

Example:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print (IsInArray("ghi", arr) > -1)
End Sub

If you have a specific example in mind, please update your question with it, otherwise example code might not apply to your situation.

More Related Contents:

  1. VBA array sort function?
  2. Excel-VBA – Insert new first column in datafield array without loops or API calls
  3. Creating an Array from a Range in VBA
  4. How to find if an array contains a string [duplicate]
  5. How do I slice an array in Excel VBA?
  6. Sorting a multidimensionnal array in VBA
  7. How do I set up a “jagged array” in VBA?
  8. Matching values in string array
  9. Declare and Initialize String Array in VBA
  10. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  11. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  12. Return Index of an Element in an Array Excel VBA
  13. Writing an array to a range. Only getting first value of array
  14. Excel VBA: Range to String Array in 1 step
  15. Faster way of using Sumifs()
  16. How to initialize a multidimensional array variable in vba for excel
  17. VBA using ubound on a multidimensional array
  18. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  19. Why am I having issues assigning a Range to an Array of Variants
  20. Subscript out of range error with an array – no idea why?
  21. VBA Excel large data manipulation taking forever
  22. Performance difference between looping range vs looping array
  23. Dynamically Dimensioning A VBA Array?
  24. Create Chart from Array data and not range
  25. VBA – Returning array from Property Get
  26. How do I Merge two Arrays in VBA?
  27. Excel VBA Quickest way to sort an array of numbers in descending order?
  28. Simple VBA array join not working
  29. Excel VBA – How to Redim a 2D array?
  30. ReDim Preserve “Subscript Out of Range”

Leave a Comment