Is it possible to use cells in vba function in arrays place?-CodePudding

I saw a function here that allowed me to identify substrings in a given string, however I don´t know how to use that function in a worksheet with cells I select. Here is the code:

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

I want to select a cell to define my "StringToBeFound" and a range to define "MyArray"

Any help gratefully appreciated :)

CodePudding user response：

You could use:

Function IsInArray2(StringToBeFound As String, MyArray As Range) As Boolean
IsInArray2 = False
dim cell as range
For each cell in myarray.cells
    If LCase(StringToBeFound) Like LCase("*" & cell.value & "*") Then
    IsInArray2 = True 'will match MyArray to any substring of StringToBeFound
    Exit Function
    End If
Next
End Function

CodePudding user response：

The following would work:

Function IsInArray2(StringToBeFound As String, MyArray As Range) As Boolean
    IsInArray2 = Not (MyArray.Find(StringToBeFound) Is Nothing)
End Function

CodePudding user response：

IsInRange UDF

Returns a boolean indicating whether a string (StringToBeFound) is contained in any of the cells of a range (MyRange).

Function IsInRange( _
    ByVal StringToBeFound As String, _
    ByVal MyRange As Range) _
As Boolean

    Dim rCount As Long: rCount = MyRange.Rows.Count
    Dim cCount As Long: cCount = MyRange.Columns.Count
    
    Dim Data() As Variant
    
    If rCount * cCount = 1 Then ' one cell
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = MyRange.Value
    Else ' multiple cells
        Data = MyRange.Value
    End If
    
    Dim r As Long, c As Long
    
    For r = 1 To rCount
        For c = 1 To cCount
            If InStr(1, CStr(Data(r, c)), StringToBeFound, vbTextCompare) > 0 _
                Then IsInRange = True: Exit Function
        Next c
    Next r

End Function