Application.Match doesn work - types incompatible-CodePudding

I have a table with strings, and I want to check whether those strings are already stored as elements in a certain array. If not, they're supposed to be added as the last element of the respective array. For some reason, I receive an error stating that the types are incompatible in the line mtch = Application.Match(srch, arr, 0).

Also, I want to work with this approach and not a different one since this is supposed to be the basis for further checks.

Sub Test_4()

Dim i, j, k As Long
Dim arr As Variant
Dim srch, mtch As String
    
With Worksheets("table1")

    For i = 1 To .Range("A1").End(xlDown).Row
    
        srch = .Range("A" & i).Value
        mtch = Application.Match(srch, arr, 0)
    
        If Not IsNumeric(mtch) Then

            ReDim Preserve arr(UBound(arr)   1)
            arr(UBound(arr)) = mtch

        End If
    
    Next i

End With

End Sub

CodePudding user response：

Your base fault is - as Mate wrote - that arr isn't initialized in the first run

You can use this code - it uses the VBA Filter function to test wether a value is already part of an array or not.

Public Function getUniqueValuesFromRange(rg As Range) As Variant

Dim arrResult As Variant
ReDim arrResult(0 To rg.Cells.Count - 1)    'dim arrResult to the max

Dim iCell As Long, iResult As Long
Dim value As Variant

For iCell = 1 To rg.Cells.Count
    value = rg.Cells(iCell)
    If UBound(Filter(arrResult, value)) = -1 Then 'value is not part of arrResult
        arrResult(iResult) = value
        iResult = iResult   1
    End If
Next

'it is "cheaper" to redim the array once at the end of the function
ReDim Preserve arrResult(iResult - 1)
getUniqueValuesFromRange = arrResult
End Function

You can call this function like this: arr = getUniqueValuesFromRange(Worksheets("table1").UsedRange.Columns("A"))

EDIT: you can use

If Not IsNumeric(Application.Match(value, arrResult, 0)) Then

instead of

If UBound(Filter(arrResult, value)) = -1 Then

If you have Excel 365 you can use the UNIQUE function as well

Public Function getUniqueValuesFromRange(rg As Range) As Variant 
With Application.WorksheetFunction
   getUniqueValuesFromRange= .Transpose(.Unique(rg))
End With
End Function

Be aware: there is no check, that you pass only one column ...