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 62 63 64 65 66 67 68 69 70 71 72
| Sub ControleVirement()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim NOmecart As Variant
Dim NomReglement As Variant
Dim ligne As Range
Dim j As Integer
Dim i As Integer
Dim MONTANTAREPORTER
Dim r As Range
Dim s As Range
Dim DerniereLigne2 As Integer
Dim DerniereLigne As Integer
Dim NomDelaFeuille As String
Dim NomDelaFeuille2 As String
'saisie obligatoire
NomDelaFeuille = InputBox("Entrez le Nom de la Feuille t A Modifier")
Worksheets(NomDelaFeuille).Activate
Set r = Range("A1:R2948")
'ON DETERMINE LA DERNIERE LIGNE ACTIVE
DerniereLigne = ActiveSheet.UsedRange.Row - 1
DerniereLigne = DerniereLigne + ActiveSheet.UsedRange.Rows.Count
NomDelaFeuille2 = InputBox("Entrez le Nom de la Feuille A controler")
Application.ScreenUpdating = False
Worksheets(NomDelaFeuille2).Activate
Set s = Range("A1:R2948")
DerniereLigne2 = ActiveSheet.UsedRange.Row - 1
DerniereLigne2 = DerniereLigne2 + ActiveSheet.UsedRange.Rows.Count
'-------------------------------------------------------------------------------------------------
Set sh1 = Worksheets(NomDelaFeuille) 'VIREMENT
Set sh2 = Worksheets(NomDelaFeuille2) 'A controler
For i = 2 To DerniereLigne
For j = 2 To DerniereLigne2
'Je controle la Période
If sh1.Cells(i, 4).Value = sh2.Cells(i, 4).Value Then
'Je controle le Nir et la Quantité Facturée
If sh1.Cells(i, 3).Value = sh2.Cells(j, 3).Value And sh1.Cells(i, 6).Value = sh2.Cells(j, 6).Value Then
'je surligne
sh1.Cells(i, 3).EntireRow.Interior.ColorIndex = 37
sh2.Cells(j, 3).EntireRow.Interior.ColorIndex = 37
' Exit For
' sh2.Cells(j, 5).Value = sh1.Cells(i, 5).Value
Else
End If
End If
Next j
Next i
MsgBox ("copie terminée")
Application.ScreenUpdating = True
Set r = Nothing
End Sub |
Partager