Filter Method for Array

Question

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

WarrantyCodeFilter won't throw an error. Per documentation: "The array returned by the Filter function contains only enough elements to contain the number of matched items." — findwindow
– findwindow, Commented Apr 15, 2022 at 15:41
Note that Filter also matches on substrings, so you'll need a different approach if you want an exact match. — Tim Williams
– Tim Williams, Commented Apr 15, 2022 at 16:02
If your terms exist in a Range on a worksheet I'd use Match() against the range (you could use Match against the array, but it would be slower than against the range). If you only have them as an array then a simple loop to check each element would be fine. — Tim Williams
– Tim Williams, Commented Apr 15, 2022 at 16:09

Storax · Accepted Answer · 2022-04-15 15:04:23Z

1

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

edited Apr 15, 2022 at 15:04

answered Apr 15, 2022 at 14:59

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gary Nolan Over a year ago

Thanks. It did the opposite and added one if it was an array, but I was able to flip it to work properly

Storax Over a year ago

I am not sure what you mean but if you fixed your problem it might be useful you add it here as a solution.

Gary Nolan Over a year ago

My variable BadWar was supposed to plus 1 every time it wasn't an array, but with the code as you had it, it added one each time it was an array. Just rewrote the line you gave me to: 'If IsArrayEmpty(WarrantyCodeFilter) = True Then BadWar = BadWar + 1'

Gary Nolan · Accepted Answer · 2022-04-15 16:56:05Z

0

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.

answered Apr 15, 2022 at 16:56

Gary Nolan

11111 bronze badges

Collectives™ on Stack Overflow

Filter Method for Array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related