le___destin
XLDnaute Occasionnel
Je veux créer une colone d'un tableau lorsque je rempli la premiere case emplie automatiquement les autre cellule jusqu'à la date du fin du moi
Private Sub Worksheet_Calculate()
Dim cellule As Range
Application.EnableEvents = False
If Application.CountIf([F12:F14], "- -") = 3 Then
Application.DisplayAlerts = False
[G12:G14].Copy [O9]
[G12:G14].Validation.Delete
[G12:G14].MergeCells = True
Application.DisplayAlerts = True
GoTo Fin
End If
If [G12:G14].MergeCells = True Then
[G12:G14].MergeCells = False
[O9:O12].Copy [G12]
End If
For Each cellule In [F12:F14]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Fin:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
Dim cellule As Range
Application.EnableEvents = False
If Application.CountIf([F12:F14], "- -") = 3 Then
Application.DisplayAlerts = False
[G12:M14].Validation.Delete
[G12:M14].MergeCells = True
[V33:AB35].Copy [G12:M14]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G12:M14].MergeCells = True Then
[G12:M14].MergeCells = False
[V12:AB14].Copy [G12:M14]
End If
For Each cellule In [F12:F14]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
'--------------------------------------------------------------------------
Dim cellul As Range
Application.EnableEvents = False
If Application.CountIf([F15:F17], "- -") = 3 Then
Application.DisplayAlerts = False
[G15:M17].Validation.Delete
[G15:M17].MergeCells = True
[V36:AB38].Copy [G15:M17]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G15:M17].MergeCells = True Then
[G15:M17].MergeCells = False
[V15:AB17].Copy [G15:M17]
End If
For Each cellul In [F15:F17]
If cellul.Value = "- -" Then
cellul.EntireRow.Hidden = True
Else
cellul.EntireRow.Hidden = False
End If
Next cellul
Fin:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
Dim cellule As Range
Application.EnableEvents = False
If Application.CountIf([F12:F14], "- -") = 3 Then
Application.DisplayAlerts = False
[G12:M14].Validation.Delete
[G12:M14].MergeCells = True
[V33:AB35].Copy [G12:M14]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G12:M14].MergeCells = True Then
[G12:M14].MergeCells = False
[V12:AB14].Copy [G12:M14]
End If
For Each cellule In [F12:F14]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
'--------------------------------------------------------------------------
Application.EnableEvents = False
If Application.CountIf([F15:F17], "- -") = 3 Then
Application.DisplayAlerts = False
[G15:M17].Validation.Delete
[G15:M17].MergeCells = True
[V36:AB38].Copy [G15:M17]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G15:M17].MergeCells = True Then
[G15:M17].MergeCells = False
[V15:AB17].Copy [G15:M17]
End If
For Each cellule In [F15:F17]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
'-----------------------------------------------------------------------------------
Application.EnableEvents = False
If Application.CountIf([F18:F20], "- -") = 3 Then
Application.DisplayAlerts = False
[G18:M20].Validation.Delete
[G18:M20].MergeCells = True
[V33:AB35].Copy [G18:M20]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G18:M20].MergeCells = True Then
[G18:M20].MergeCells = False
[V18:AB20].Copy [G18:M20]
End If
For Each cellule In [F18:F20]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
'--------------------------------------------------------------------------
Application.EnableEvents = False
If Application.CountIf([F21:F23], "- -") = 3 Then
Application.DisplayAlerts = False
[G21:M23].Validation.Delete
[G21:M23].MergeCells = True
[V36:AB38].Copy [G21:M23]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G21:M23].MergeCells = True Then
[G21:M23].MergeCells = False
[V15:AB17].Copy [G21:M23]
End If
For Each cellule In [F21:F23]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
'------------------------------------------------------------------------------
Application.EnableEvents = False
If Application.CountIf([F24:F26], "- -") = 3 Then
Application.DisplayAlerts = False
[G24:M26].Validation.Delete
[G24:M26].MergeCells = True
[V33:AB35].Copy [G24:M26]
Application.DisplayAlerts = True
GoTo Fin
End If
If [G24:M26].MergeCells = True Then
[G24:M26].MergeCells = False
[V18:AB20].Copy [G24:M26]
End If
For Each cellule In [F24:F26]
If cellule.Value = "- -" Then
cellule.EntireRow.Hidden = True
Else
cellule.EntireRow.Hidden = False
End If
Next cellule
Application.EnableEvents = True
Fin:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
Dim C As Range, ResC As String, Cellule As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In Range("F12", Cells(Rows.Count, 6).End(xlUp))
If C.Offset(, -2) <> "" And C.Offset(, -2) <> ResC Then
ResC = C
If Application.CountIf(C.Resize(3), "- -") = 3 Then
Application.DisplayAlerts = False
C.Offset(, 1).Resize(3).Copy Cells(C.Row, 15)
C.Offset(, 1).Resize(3).Validation.Delete
C.Offset(, 1).Resize(3).MergeCells = True
Application.DisplayAlerts = True
GoTo Fin
End If
If C.Offset(, 1).Resize(3).MergeCells = True Then
C.Offset(, 1).Resize(3).MergeCells = False
Cells(C.Row, 15).Resize(3).Copy C.Offset(, 1)
End If
For Each Cellule In C.Resize(3)
If Cellule.Value = "- -" Then
Cellule.EntireRow.Hidden = True
Else
Cellule.EntireRow.Hidden = False
End If
Next Cellule
End If
Next C
Fin:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub