Checking an array for both InStr and equal to something

gers1978 · Jul 14th 2016

I have an array of strings that I want to check a variable, strName, against, but for some of the values in the array I just want to see if strName exists anywhere within the array element. For other elements I want to check if strName is equal to the entirety of the array element.

Do I need 2 arrays, one for the those elements I need to check using InStr and one for those I need to check using equals? Or is there a way of doing this with a single array?

Thanks

Also posted here: http://www.mrexcel.com/forum/e…omething.html#post4576931

**KjBox** · Jul 14th 2016

Re: Checking an array for both InStr and equal to something

Maybe something like this

Code

Sub Test()
    Dim x, Entires, i As Long, ii As Integer, strName As String
    
    Entires = Array("aaa", "bbb", "ccc", "123", "1a3", "xyz") 'Load array Entires with strings that need to match an entire element of array x
                                                                        
    ' Load array x as per your existing code


    strName = "???" 'Assign required string to the variable strName
    
    For i = 1 To UBound(x, 1)
        If Not IsError(Application.Match(strName, Entires, 0)) Then
            If CStr(x(i, 1)) = strName Then
                ' code here to do what you want if entire match found
            End If
        ElseIf InStr(1, strName ,CStr(x(i, 1))) Then
            ' code here to do what you want if In String match found
        Else
             ' code here to do what you want if no In String match and no Entire match found
        End If
    Next
    
End Sub

Display More

The use of CStr() ensures that both text and numeric elements in array x will be checked without error.

Checking an array for both InStr and equal to something

Participate now!

Share