Filter Method for Array-CodePudding

In my code, I've declared a variable "Warranty Code" as a variant, and then populated that variant as an array with 41 potential 2 character string values.

Now I'm looping through a range, and checking each cell in that range (the "MWWar" variable), to see if it matches one of the 41 strings in my array.

If it does, I want my code to do nothing, but if it doesn't, I want it to add one to my counter (the "BadWar" variable).

If the "WarrantyCodeFilter" variable does match, the code works fine and does nothing but go onto the next line. However, if it doesn't find a match in my array, it becomes "subscript out of range" error 9, and won't let me proceed.

I've tried the "If(Iserror) = true" to add to my counter, but it still wouldn't let me do it

How can I get the no match scenario to just add to my counter, and move on to the next line instead of erroring out?

For i = 2 To lr
    Set MWWar = Cells(i, MWWarCol)
    WarrantyCodeFilter = Filter(WarrantyCode, MWWar, True, vbTextCompare)
    If IsError(WarrantyCodeFilter(0)) = True Then BadWar = BadWar   1
Next i

CodePudding user response：

You could use the function IsArrayEmpty from Chip Pearson's website in order toc check if the filter gave a result

If Not IsArrayEmpty(WarrantyCodeFilter) Then
    'If IsError(WarrantyCodeFilter(0)) = True Then BadWar = BadWar   1
    BadWar = BadWar   1
End If

Public Function IsArrayEmpty(Arr As Variant) As Boolean    
    ' IsArrayEmpty
    ' This function tests whether the array is empty (unallocated). Returns TRUE or FALSE.
    '
    ' The VBA IsArray function indicates whether a variable is an array, but it does not
    ' distinguish between allocated and unallocated arrays. It will return TRUE for both
    ' allocated and unallocated arrays. This function tests whether the array has actually
    ' been allocated.
    '
    ' This function is really the reverse of IsArrayAllocated.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim LB As Long
    Dim UB As Long
    
    Err.Clear
    On Error Resume Next
    If IsArray(Arr) = False Then
        ' we weren't passed an array, return True
        IsArrayEmpty = True
    End If
    
    ' Attempt to get the UBound of the array. If the array is
    ' unallocated, an error will occur.
    UB = UBound(Arr, 1)
    If (Err.Number <> 0) Then
        IsArrayEmpty = True
    Else
        ''''''''''''''''''''''''''''''''''''''''''
        ' On rare occassion, under circumstances I
        ' cannot reliably replictate, Err.Number
        ' will be 0 for an unallocated, empty array.
        ' On these occassions, LBound is 0 and
        ' UBoung is -1.
        ' To accomodate the weird behavior, test to
        ' see if LB > UB. If so, the array is not
        ' allocated.
        ''''''''''''''''''''''''''''''''''''''''''
        Err.Clear
        LB = LBound(Arr)
        If LB > UB Then
            IsArrayEmpty = True
        Else
            IsArrayEmpty = False
        End If
    End If
    
    End Function

CodePudding user response：

So, after some more digging, I found a significantly easier way to achieve my goal.

Instead of using the custom function as suggested above, and adding line:

If Not IsArrayEmpty(WarrantyCodeFilter)

I was able to incorporate the Match function as suggested by @TimWilliams in this manner

WarrantyCodeFilter = Not IsError(Application.Match(MWWar, WarrantyCode, 0))

Declared WarrantyCodeFilter as Boolean. Matched my cell (MWWar) against the array (WarrantyCode). This line returns true or false if the item is or isn't in my array. From there, I can do what I need with my counter.