CODEWITHSUNDEEP
arraysexcelwildcardselect-casevba
Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

Check if string is in array or not including wildcards with Excel VBA

I found this function to identify whether a string is in a given array, however it does not seem to be able to handle wildcards (or atleast not the way i do it).

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

My use of the function:

Dim BannedWildcards() As Variant

BannedWildcards = Array("", "-", "?", 0, "na", "n/a", _
"*account*", "*hse*", "*defined*", "*applicable*", "*operation*", "*action*", "*manager*")

            Select Case True
            Case IsInArray(LCase(Sht_Tracker.Cells(RowCounter_CRCT, 17)), BannedWildcards) = True
                Arr_Dashboard_Current(10, ArrC_Dashboard_Current) = "#N/A"
            Case Else: Arr_Dashboard_Current(10, ArrC_Dashboard_Current) = Sht_Tracker.Cells(RowCounter_CRCT, 17)
            End Select

Upvotes: 0

Views: 1450

Answers (2)

Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

Thank you SLWS. I modified the code you referenced to a bit in order to fit my need.
This works for me:

Function IsInArray(stringToBeFound As String, MyArray As Variant) As Boolean

    Dim i As Long
    Dim WildCard As String
    WildCard = "*"

    IsInArray = False

    For i = LBound(MyArray) To UBound(MyArray)
        If InStr(MyArray(i), WildCard) > 0 Then
            If LCase(stringToBeFound) Like LCase("*" & Replace(MyArray(i), " ", "*") & "*") Then
                IsInArray = True
                Exit Function
            End If
        Else
            If LCase(stringToBeFound) = LCase(MyArray(i)) Then
                IsInArray = True
                Exit Function
            End If
        End If
    Next
End Function

Upvotes: 0

SLWS
SLWS

Reputation: 546

Or something like:

Function IsInArray2(StringToBeFound As String, MyArray As Variant) As Boolean
IsInArray2 = False
wildCard = "*"
If InStr(StringToBeFound, wildCard) > 0 Then
    For i = LBound(MyArray) To UBound(MyArray)
        If "*" & MyArray(i) & "*" Like StringToBeFound Then IsInArray2 =    True 'will match MyArray to any substring of StringToBeFound
    Next
Else
    For i = LBound(MyArray) To UBound(MyArray)
            If MyArray(i) == StringToBeFound Then IsInArray2 =  True 'will exactly match MyArray to StringToBeFound
    Next
End If
End Function

Upvotes: 1

Related Questions