Thread: Array Filtering

Hello Everyone!

I have a worksheet with a sheet that has a table. I need to create an array out of that sheet's column A, but with criteria.

Here is what I have... this gives me the array, but I cannot seem to add criteria. You can see the criteria I need to add in the bottom. I think it isn't working because of the mismatched variables. How do I say with the current cell we are looking at, check this offset cell against a specific value/cell?

Public Function UniqueValues(ByVal OrigArray As Variant) As Variant
    
    Dim vAns() As Variant
    Dim lStartPoint As Long
    Dim lEndPoint As Long
    Dim lCtr As Long, lCount As Long
    Dim iCtr As Integer
    Dim col As New Collection
    Dim sIndex As String
    Dim vTest As Variant, vItem As Variant
    Dim iBadVarTypes(4) As Integer
    
    'Function does not work if array element is one of the following types
    iBadVarTypes(0) = vbObject
    iBadVarTypes(1) = vbError
    iBadVarTypes(2) = vbDataObject
    iBadVarTypes(3) = vbUserDefinedType
    iBadVarTypes(4) = vbArray
    
    'Check to see whether the parameter is an array
    If Not IsArray(OrigArray) Then
        Err.Raise ERR_BP_NUMBER, , ERR_BAD_PARAMETER
        Exit Function
    End If
    
    lStartPoint = LBound(OrigArray)
    lEndPoint = UBound(OrigArray)
    For lCtr = lStartPoint To lEndPoint
        vItem = OrigArray(lCtr)
        
        'First check to see whether variable type if acceptable
        For iCtr = 0 To UBound(iBadVarTypes)
            If VarType(vItem) = iBadVarTypes(iCtr) Or _
                VarType(vItem) = iBadVarTypes(iCtr) + vbVariant Then
                    Err.Raise ERR_BT_NUMBER, , ERR_BAD_TYPE
                    Exit Function
            End If
        
        Next iCtr
        
        'Add element to a collection, using it as the index
        'If an error occurs, the element already exists
        sIndex = CStr(vItem)
        
        '//// need to filter by this....Range(vItem).Offset(0, 11)=XYZ  (SEE BELOW)
        
        'First element, add automatically
        If lCtr = lStartPoint Then
            col.Add vItem, sIndex
            ReDim vAns(lStartPoint To lStartPoint) As Variant
            vAns(lStartPoint) = vItem
        '////// this is where i put the filtering and it isn't working correctly.....------------------------------------------
        ElseIf Range(vItem).Offset(0, 11) = "XYZ" Then
            On Error Resume Next
            col.Add vItem, sIndex
            If Err.Number = 0 Then
                lCount = UBound(vAns) + 1
                ReDim Preserve vAns(lStartPoint To lCount)
                vAns(lCount) = vItem
            End If
        End If
        Err.Clear
    Next lCtr
    UniqueValues= vAns
                    
End Function

Any help would be much appreciated!!!