Simplifier le code vba

[Ilino]

Bonsoir forum,
je souhaiterai simplifie le code ci dessous .

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [L41] = "Oui" Then [N41] = "Non"
If [L43] = "Oui" Then [N43] = "Non"
If [L45] = "Oui" Then [N45] = "Non"
If [L47] = "Oui" Then [N47] = "Non"
If [L49] = "Oui" Then [N49] = "Non"
If [L51] = "Oui" Then [N51] = "Non"
If [L53] = "Oui" Then [N53] = "Non"
If [L55] = "Oui" Then [N55] = "Non"
If [L57] = "Oui" Then [N57] = "Non"
If [L59] = "Oui" Then [N59] = "Non"
If [L61] = "Oui" Then [N61] = "Non"
If [L63] = "Oui" Then [N63] = "Non"
If [L65] = "Oui" Then [N65] = "Non"
If [L67] = "Oui" Then [N67] = "Non"
If [L69] = "Oui" Then [N69] = "Non"
If [L71] = "Oui" Then [N71] = "Non"
If [L73] = "Oui" Then [N73] = "Non"
If [L77] = "Oui" Then [N77] = "Non"
If [L79] = "Oui" Then [N79] = "Non"
If [L81] = "Oui" Then [N81] = "Non"
If [L85] = "Oui" Then [N85] = "Non"
If [L87] = "Oui" Then [N87] = "Non"
If [L89] = "Oui" Then [N89] = "Non"
If [L91] = "Oui" Then [N91] = "Non"
If [L93] = "Oui" Then [N93] = "Non"

If [F21] = "Non" Then [H21] = "Pas d'ouvrage"
If [F23] = "Non" Then [H23] = "Pas d'ouvrage"
If [F25] = "Non" Then [H25] = "Pas d'ouvrage"

End Sub

GRAZIE

 

[VIARD]

Re : Simplifier le code vba

Bonjour Ilino

je vois comme ceci

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dim i%

for i=41 to 93 step 2
     if range("L" & i).value="OUI" then range("N" & i).value="NON"
next i
If [F21] = "Non" Then [H21] = "Pas d'ouvrage"
If [F23] = "Non" Then [H23] = "Pas d'ouvrage"
If [F25] = "Non" Then [H25] = "Pas d'ouvrage"

End Sub

non testé

A+

Jean-Paul

 

[kingfadhel]

Re : Simplifier le code vba

Testé sur mon PC

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For i = 41 To 93 Step 2
If Range("L" & i) = "Oui" Then
Range("N" & i) = "Non"
End If
Next
For j = 21 To 25 Step 2
If Range("F" & j) = "Non" Then
Range("H" & j) = "Pas d'ouvrage"
End If
Next
End Sub

 

[kingfadhel]

Re : Simplifier le code vba

de même sans le (End if)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For i = 41 To 93 Step 2
If Range("L" & i) = "Oui" Then Range("N" & i) = "Non"
Next
For j = 21 To 25 Step 2
If Range("F" & j) = "Non" Then Range("H" & j) = "Pas d'ouvrage"
Next
End Sub

 

[laetitia90]

Re : Simplifier le code vba

bonjour tous 🙂🙂

une autre facon de l'ecrire

Option Compare Text 'minuscule ou majuscule
Private Sub Worksheet_SelectionChange(ByVal T As Range)
 If Not Intersect(T, [l41:l93]) Is Nothing And T.Count = 1 Then _
 If Not T.Row Mod 2 = 0 And T = "Oui" Then T.Offset(0, 2) = "Non"
 If Not Intersect(T, [f21:f25]) Is Nothing And T.Count = 1 Then _
 If Not T.Row Mod 2 = 0 And T = "Non" Then T.Offset(0, 2) = "Pas d'ouvrage"
End Sub