VBA Search an array inside an array? (Check if all items of one array exists in another array)

Please, look at the next example. Is this what you try accomplishing?

Sub testArrInArr()
  Dim arr(), arr1(), arr2(), arr3(), arr4()
  arr1 = Array(1, 2, 3): arr2 = Array(2, 3, 4)
  arr3 = Array(3, 6, 5, 4): arr4 = Array(4, 5, 6)
  
  arr = Array(arr1, arr2, arr3)
  Debug.Print arrIsInArray(arr, arr2)
End Sub

Function arrIsInArray(arr As Variant, arrX As Variant) As Boolean
   Dim i As Long, jArr As String
    For i = LBound(arr) To UBound(arr)
        If Join(arr(i)) = Join(arrX) Then arrIsInArray = True: Exit Function
    Next i
End Function

Edited:

In order to test each array element if exists in another array, plese try the next way:

Sub tst2CheckArrElements()
  Dim arr, arr1, arr2
  arr = Split("1,2,3,4,5", ","): arr1 = Split("Sausage,Dog,Ship", ","): arr2 = Split("1,3,2", ",")
  
  Debug.Print arrElemInArray(arr, arr1)
  Debug.Print arrElemInArray(arr, arr2)
End Sub

Function arrElemInArray(arr As Variant, arrX As Variant) As Boolean
   Dim i As Long, j As Long, boolFound As Boolean, mtch

   If Not IsArray(arrX) Then
         For j = LBound(arr) To UBound(arr)
            If CStr(arr(j)) = CStr(arrX) Then arrElemInArray = True: Exit For
        Next j
        Exit Function
   End If
   For i = LBound(arrX) To UBound(arrX, 2)
        For j = LBound(arr) To UBound(arr)
            If CStr(arr(j)) = CStr(arrX(1, i)) Then
                boolFound = True: Exit For
            End If
        Next j
        If Not boolFound Then arrElemInArray = False: Exit Function
        boolFound = False
   Next i
   arrElemInArray = True   
End Function

Is Array In Array

Personalized Study

• Change the number format of the cells containing the values to general or to a numeric format to make it work.

Option Explicit

Sub Testing()

    Const sHeader As String = "Parameters"
    Const sProperty As String = "Lower Film Width (mm)"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Machine Specification")

    ' Reference the last cell of the used range.
    Dim LastCell As Range
    With ws.UsedRange
        Set LastCell = .Cells(.Rows.Count, .Columns.Count)
        Debug.Print "UsedRange:        " & .Address(0, 0)
        Debug.Print "LastCell:         " & LastCell.Address(0, 0)
    End With
    
    ' Reference the header cell.
    Dim HeaderCell As Range
    Set HeaderCell = ws.Cells.Find(sHeader, LastCell, xlFormulas, xlWhole)
    If HeaderCell Is Nothing Then Exit Sub ' header not found
    Debug.Print "HeaderCell:       " & HeaderCell.Address(0, 0)
    
    ' Calculate the first column number.
    Dim FirstColumn As Long: FirstColumn = HeaderCell.Column + 1
    Debug.Print "FirstColumn:      " & FirstColumn
    
    ' Calculate the last column number.
    Dim LastColumn As Long: LastColumn = _
        ws.Cells(HeaderCell.Row, ws.Columns.Count).End(xlToLeft).Column
    If LastColumn < FirstColumn Then Exit Sub ' no data to the right of header
    Debug.Print "LastColumn:       " & LastColumn
    
    ' Reference the column range below the header cell
    ' to search for the property.
    Dim sDataColumnRange As Range ' below the header
    Set sDataColumnRange _
        = HeaderCell.Resize(LastCell.Row - HeaderCell.Row).Offset(1)
    Debug.Print "sDataColumnRange: " & sDataColumnRange.Address(0, 0); ""
    
    ' Reference the property cell.
    Dim sPropertyCell As Range
    With sDataColumnRange
        Set sPropertyCell _
            = .Find(sProperty, .Cells(.Rows.Count), xlFormulas, xlWhole)
        If sPropertyCell Is Nothing Then Exit Sub ' property not found
        Debug.Print "sPropertyCell:    " & sPropertyCell.Address(0, 0)
    End With
    
    ' Reference the property (values) row range (first to last column).
    Dim PropertyRowRange As Range
    Set PropertyRowRange = ws.Range(ws.Cells(sPropertyCell.Row, FirstColumn), _
        ws.Cells(sPropertyCell.Row, LastColumn))
    Debug.Print "PropertyRowRange: " & PropertyRowRange.Address(0, 0)
    Debug.Print "PropertyRowRange Values" & vbLf & Join(Application.Transpose( _
        Application.Transpose(PropertyRowRange.Value)), ", ")
    
    ' Populate the property values array.
    Dim PropertyValuesArray As Variant
    PropertyValuesArray = Application.Transpose(Evaluate("Row(320:420)"))
    Debug.Print "PropertyValuesArray Values"
    Debug.Print Join(PropertyValuesArray, ", ")
    
    ' Return the result whether all values of the property row range
    ' are found in the property values array.
    If IsRowInArr(PropertyValuesArray, PropertyRowRange) Then
        MsgBox "All matching.", vbInformation
        Debug.Print "All matching."
    Else
        MsgBox "Not all matching.", vbCritical
        Debug.Print "Not all matching."
    End If

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether a 1D array ('InArr')
'               contains all values in a row ('RowIndex')
'               of a range ('IsRange').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsRowInArr( _
    ByVal InArr As Variant, _
    ByVal IsRange As Range, _
    Optional ByVal RowIndex As Long = 1) _
As Boolean
    Const ProcName As String = "IsRowInArr"
    On Error GoTo ClearError
    
    With IsRange.Rows(RowIndex)
        Dim cCount As Long: cCount = .Columns.Count
        If cCount = 1 Then
            IsRowInArr = IsNumeric(Application.Match(.Value, InArr, 0))
        Else
            Dim IsRow As Variant: IsRow = .Value
            IsRowInArr = Application.Count(Application.Match( _
                IsRow, InArr, 0)) = cCount
        End If
    End With

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function

Initial Answer

• The function will return true if all the elements of an array (IsArr) are found in another array (InArr).

Option Explicit

Sub IsArrayInArrayTEST()
    
    Dim InArr As Variant: InArr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    
    Dim IsArr As Variant
    
    IsArr = Array(1)
    Debug.Print IsArrayInArray(IsArr, InArr) ' True
    
    IsArr = Array(1, 5, 11)
    Debug.Print IsArrayInArray(IsArr, InArr) ' False

End Sub

Function IsArrayInArray( _
    ByVal IsArr As Variant, _
    ByVal InArr As Variant) _
As Boolean
    
    Dim IsCount As Long: IsCount = UBound(IsArr) - LBound(IsArr) + 1
    Dim rArr As Variant: rArr = Application.Match(IsArr, InArr, 0)
    Dim rCount As Long: rCount = Application.Count(rArr)
    'Debug.Print rCount, IsCount
    If rCount = IsCount Then
        IsArrayInArray = True
    End If
    
End Function