1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
| Sub comparaison()
'comparaison entre la feuille copie_save et la feuille bdd
Dim wsh1 As Worksheet, wsh2 As Worksheet, wsh3 As Worksheet
Dim ligne As Long, LastLig1 As Long, LastLig3 As Long, LastLig2 As Long, result As Long
Dim C As Range
Dim i As Integer, j As Integer
Set wsh1 = Worksheets("bdd")
Set wsh2 = Worksheets("Copie-save")
Set wsh3 = Worksheets("sortie-reliquat")
LastLig1 = wsh1.Cells(Rows.Count, "A").End(xlUp).Row 'calclul le nombre de ligne max de la feuille bdd
LastLig2 = wsh2.Cells(Rows.Count, "A").End(xlUp).Row 'calclul le nombre de ligne max de la feuille copie-save
LastLig3 = wsh3.Cells(Rows.Count, "A").End(xlUp).Row 'calclul le nombre de ligne max de la feuille sortie-reliquat
result = Application.WorksheetFunction.Max(LastLig1, LastLig2)
For i = 0 To 2 Step 1 'copie des trois premieres ligne d'en-tête
wsh2.Rows(i + 1).Copy _
Destination:=wsh3.Rows(LastLig3 + i)
Next i
For ligne = 4 To result Step 1
Set C = wsh1.Columns("B").Find(wsh2.Cells(ligne, "C").Value, LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then 'si ref commande trouvé
LastLig3 = wsh3.Cells(Rows.Count, "A").End(xlUp).Row 'calclul le nombre de ligne max de la feuille sortie-reliquat
wsh2.Rows(ligne).Copy _
Destination:=wsh3.Rows(LastLig3 + 1)
LastLig3 = 0
End If
Next ligne
ligne = 0
For ligne = 2 To result Step 1
For j = 4 To result Step 1
Set C = wsh1.Cells(ligne, "B").Find(wsh2.Cells(j, "C").Value, LookIn:=xlValues, lookat:=xlWhole)
'lig = ligne
If C Is Nothing Then 'si pas trouvé ref commande
MsgBox (j)
LastLig3 = wsh3.Cells(Rows.Count, "A").End(xlUp).Row 'calclul le nombre de ligne max de la feuille sortie-reliquat
wsh1.Rows(ligne).Copy _
Destination:=wsh3.Rows(LastLig3 + 1)
LastLig3 = 0
End If
Next j
Next ligne
ligne = 0
Set wsh1 = Nothing
Set wsh2 = Nothing
Set wsh3 = Nothing
Set C = Nothing
End Sub |
Partager