Filter VBA function

Question

I am using the code below to check if an element is in a matrix related to a Range. It happens that in some cases the result is = 0 demonstrating that it exists, but does not exist.

What’s wrong? Instead of this function you should run through the matrix through a Loop?

If UBound(Filter(canc, Pl.Cells(x, "b").Value, , vbBinaryCompare)) <> -1

Answer 1

I have already done this search function but with LOOP using FOR in the Matrix. Just inform the word that should be found and the table in which the search will be performed.

Function LoopNaTabela(palavra As String, Tb)
Dim Dimenssao As Byte, i As Long, j As LongOn Error Resume Next
If IsError(UBound(Tb, 2)) Then Dimensao = 1 Else Dimensao = 2
On Error GoTo 0
Select Case Dimensao
    Case 2
        For i = LBound(Tb, 1) To UBound(Tb, 1)
            For j = LBound(Tb, 2) To UBound(Tb, 2)
                If Tb(i, j) = palavra Then LoopNaTabela = True: Exit Function
            Next j
        Next i
End Select
End Function

In this format it will go through the whole matrix in search of the specific word, never had performance problems with it.

If you want to test other ways, in this link: Check word

has enough content and example on the subject