1

This recordset needs to compare with value getting inside For Each :

Do While Not rs.EOF
    Debug.Print Trim(rs!CodeNr)
    rs.MoveNext
Loop

Result: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

For Each idx In td.Indexes
    If InStr(1, idx.Fields, "+ScanSpe_") = 1 Then
        Debug.Print ExtractNumber(idx.Fields)
    End If
Next

Result: 1 10 11 12 13 14 15 16 2 3 4 5 6 7 8 9

How can check these two results that I know which one is a match or no match? Any help would be appriciated.

Abzal Ali
  • 153
  • 1
  • 12
  • VBA does not offer a simple way to do it. You could insert the values of one of these two lists in a `Collection` by also specifying the value as key in the `Add` method. Then you can loop the other list and test whether the collection contains the value. The problem is that the collection will throw an exception if the value is not contained. See [this answer](https://stackoverflow.com/a/991900/880990) as a workaround. – Olivier Jacot-Descombes Dec 15 '21 at 16:39

2 Answers2

0

Open rs sorted on Str(rs!CodeNr), not in a Do .. While but a For .. Next loop.

Likewise for the ts.Indexes; use a For .. Next loop.

Then you can match the value of the counter of the first loop and the value of the counter of the second loop and check for a match of the two values.

Exactly how depends on, if you wish to find matches or no-matches, and if the two counts of elements in the two collections are equal or not.

Gustav
  • 53,498
  • 7
  • 29
  • 55
0

I tried myself and this piece of code worked quite fine.

Do While Not rs.EOF
    For Each idx In td.Indexes
        If InStr(1, idx.Fields, "+ScanSpe_") = 1 Then
            'Debug.Print ExtractNumber(idx.Fields)
            If ExtractNumber(idx.Fields) = Trim(rs!CodeNr) Then
                If rs!GS1Dupli = 0 Then
                   rs.Edit
                   rs!GS1Dupli = -1
                   rs.Update
                End If
            End If
            
        End If
    Next
    rs.MoveNext
Loop
Abzal Ali
  • 153
  • 1
  • 12
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 15 '21 at 17:17
  • It may work fine, but it has an **O(n^2)** time complexity – Olivier Jacot-Descombes Dec 15 '21 at 17:24
  • @OlivierJacot-Descombes I would like to try your suggested solution. – Abzal Ali Dec 15 '21 at 17:52