I have two tables. One table is called DRData (Blad3), other table is CheckData (Blad2). EANCODE is Column J for DRData, and Column A for Checkdata.

I want to check whether CheckData.EANCODE is present in DRData.EANCODE. If so; delete that row from CheckData.

I tried several things, but no success yet. The code I have written now is as follows:

Sub FindEAN()
Dim i As Long
Dim x As Long

x = 1 'Start on first row

EANtoFind = Blad2.Range("A" & x).Value

For i = 1 To 99999    '

    If Blad3.Cells(i, 1).Value = EANtoFind Then
    Blad2.Range("A" & x).EntireRow.Delete
    Else: x = x   1
    End If


Next i
End Sub

When the EANCODE is not present, I want to hop over a row to check that code. I want to end with a list in CheckData where all the EANCODE values that are not in DRData are shown. With the code above, only the first row is getting deleted and now I'm stuck how to get this to loop. Including the x 1 to get to the next row.

CodePudding user response：

First you have to clarify that your problem is a little bit complicated. You have to pay attention to indexes when deleting rows

To do that, you have to point the the maximal number of line to optimize your loop

A simple way to do that, is to use predefined search function, and edit your code a little bit.

My favorite is Application.match(), which takes 3 parameters :

1. Value to look for
2. Array where you look (in our case the column J of Blad3 or position 10)
3. 0 : exact match

For more details, see the documentation

https://docs.microsoft.com/fr-fr/office/vba/api/excel.worksheetfunction.match

An example of code which works is like the following

Sub FindEAN()

Dim x As Long

x = 1 'Start on first row

maxline = Blad2.Range("A" & Rows.count).End(xlUp).row

While x <= maxline    '
    EANtoFind = Blad2.Range("A" & x).Value
    If Not IsError(Application.Match(EANtoFind, Blad3.Columns(10), 0)) Then
        Blad2.Range("A" & x).EntireRow.Delete
        maxline = maxline - 1
    Else
        x = x   1
    End If
Wend

End Sub