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

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