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
| Const celref = "n17"
Const celnum = "P1"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ligne As Long, Plage As Range, C As Range
If Not Intersect(Target, Range(celref)) Is Nothing Then
Application.EnableEvents = False
Range(celnum).Value = Range(celnum).Value + 1
Application.EnableEvents = True
ElseIf Intersect(Target, Union([Facture], [RéfDossier], [To], [Client], [Montant], _
[DateFacture])) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Application.CountA(Union([Facture], [RéfDossier], [To], [Client], [Montant], _
[DateFacture])) < 6 Then Exit Sub
Set Plage = Union([Facture], [RéfDossier], [To], [Client], [Montant], _
[DateFacture])
If Not IsNumeric(Application.Match([Facture].Value, [CA!A:A], 0)) Then
With Sheets("CA")
Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
For i = 1 To Plage.Count
.Cells(Ligne, i) = Plage.Areas(i)
Next
End With
End If
End If
End Sub |
Partager