Find an array of values into a table-CodePudding

I'm actually facing a problem today. I'm trying to find a row in an Excel Table and return it, based on an entire array of values supposed to match the table

the table.

There's a table exemple, they always start with an ID Column that is missing from my Array.

This listbox are the data that I'm getting in my array.

I'm actually using a function that search for a perfect match of my array into a specified table. But I need to make it start on second columns of table. Here's my function.

Function checkDuplicate(ws As Worksheet, valuesArray As Variant) As Boolean

    Dim i As Long, n As Long, j As Long, z As Long
    Dim ar
    
    If ws.Name = "Interface" Or ws.Name = "Listes" Then Exit Function
    z = LBound(valuesArray)
    n = UBound(valuesArray) - z   1
    With ws
        ar = .UsedRange.Columns(1).Resize(, n)
        For i = 1 To UBound(ar)
            j = 1
            Do
                If ar(i, j) <> valuesArray(j   z - 1) Then
                    Exit Do
                End If
                j = j   1
            Loop While j <= n
            If j > n Then
                checkDuplicate = True
                Exit Function
            End If
        Next
    End With

End Function

Any help would be higlhy appreciated, Thank.

CodePudding user response：

This worked for me:

Sub Tester()
    Debug.Print checkDuplicate(ActiveSheet, Array("A", "B", "C", "D"))
End Sub

Function checkDuplicate(ws As Worksheet, valuesArray As Variant) As Boolean

    Dim i As Long, n As Long, j As Long, z As Long
    Dim ar, col As Long, sz As Long
    
    If ws.Name = "Interface" Or ws.Name = "Listes" Then Exit Function
    
    sz = UBound(valuesArray) - LBound(valuesArray)   1 'size of valuesArray
    
    'pick up data starting with second column
    ar = ws.UsedRange.Columns(2).Resize(, sz).Value
    
    For i = 1 To UBound(ar, 1)
        checkDuplicate = False
        col = 1
        For j = LBound(valuesArray) To UBound(valuesArray)
            checkDuplicate = ar(i, col) = valuesArray(j) 'match
            If Not checkDuplicate Then Exit For          'no match: stop checking
            col = col   1                                'next column in sheet array
        Next j
        If checkDuplicate Then Exit Function  'all columns matched - done searching
    Next i
End Function