XL 2016 cellules A, b, C et D sont automatiquement vides

[Dadi147]

Bonjour, je veux quand il n'y a pas de valeur dans la plage cible ("E:V"), les cellules A, b, c et d sont automatiquement vides

Private Sub Worksheet_Change(ByVal Target As Range)

Cancel = True
If Not Intersect(Target, Range("e:v")) Is Nothing Then
'If Target.Column = 5 And Cells(Target.Row, 1) = "" Then
Cells(Target.Row, 1) = Date
Cells(Target.Row, 2).Value = Application.WorksheetFunction.WeekNum(Cells(Target.Row, 1).Value, 21)
Cells(Target.Row, 3) = sheet1.Range("c2").Value
Cells(Target.Row, 4).Formula = "=IF(RC[1]&RC[4]&RC[9]&RC[2]&RC[3]&RC[5]&RC[6]&RC[7]&RC[8]&RC[10]&RC[11]&RC[12]&RC[13]&RC[14]&RC[15]&RC[16]&RC[17]&RC[18]&RC[19]&RC[20]&RC[21]&RC[22]&RC[23]&RC[24]&RC[25]="""","""",R2C10)"
Dim rng As Range
For Each rng In sheet1.UsedRange
If rng.HasFormula Then
rng.Formula = rng.Value
     End If
    Next rng


'If Not Intersect(Target, Range("e:v")) ="" Then
'Application.EnableEvents = False
'Cells(Target.Row, 1) = ""
'Cells(Target.Row, 2) = ""
'Cells(Target.Row, 3) = ""
'Cells(Target.Row, 4) = ""

Application.EnableEvents = True
End If

End Sub

 

[job75]

Bonsoir Dadi147,

je veux quand il n'y a pas de valeur dans la plage cible ("E:V"), les cellules A, b, c et d sont automatiquement vides

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
With Range("A5:V" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    If .Row < 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each r In .EntireRow
        If Application.CountA(Intersect(r, Range("A:D"))) Then _
            If Application.CountA(Intersect(r, Range("E:V"))) = 0 Then Intersect(r, Range("A:D")) = ""
    Next
    Application.EnableEvents = True
End With
End Sub

Bonne nuit.

 

[Dadi147]

Bonsoir Dadi147,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
With Range("A5:V" & Cells.SpecialCells(xlCellTypeLastCell).Row)
    If .Row < 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each r In .EntireRow
        If Application.CountA(Intersect(r, Range("A:D"))) Then _
            If Application.CountA(Intersect(r, Range("E:V"))) = 0 Then Intersect(r, Range("A:D")) = ""
    Next
    Application.EnableEvents = True
End With
End Sub

Bonne nuit.

Merci pour l'aide, mais comment puis-je le lier avec le code dans le premier message.

 

[job75]

Votre code n'a rien à voir avec le problème posé, effacez-le.