simplification macro vba

[jluc56]

Bonsoir :
J'ai écrit quelques bribes en vba; ça fonctionne, mais ça me semble un peu lourd... il y a certainement moyen de simplifier surtout de "$A$27" à "$A$48" en sachant qu'il y a un saut en 34 & 42 , j'ai tenté avec une boucle for next mais sans succès .
Ci joint mon livre !!! :

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$26" Then
Range("I28").Select
ActiveCell.FormulaR1C1 = "0%"
Range("I26").Select
Else
If Target.Address = "$I$34" Then
Range("I36").Select
ActiveCell.FormulaR1C1 = "0%"
Range("I34").Select
Else
If Target.Address = "$I$42" Then
Range("I44").Select
ActiveCell.FormulaR1C1 = "0%"
Range("I42").Select
Else
If Target.Address = "$A$27" Then
Range("D27").Select
ActiveCell.FormulaR1C1 = "1"
Range("A27").Select
Else
If Target.Address = "$A$28" Then
Range("D28").Select
ActiveCell.FormulaR1C1 = "1"
Range("A28").Select
Else
If Target.Address = "$A$29" Then
Range("D29").Select
ActiveCell.FormulaR1C1 = "1"
Range("A29").Select
Else
If Target.Address = "$A$30" Then
Range("D30").Select
ActiveCell.FormulaR1C1 = "1"
Range("A30").Select
Else
If Target.Address = "$A$31" Then
Range("D31").Select
ActiveCell.FormulaR1C1 = "1"
Range("A31").Select
Else
If Target.Address = "$A$32" Then
Range("D32").Select
ActiveCell.FormulaR1C1 = "1"
Range("A32").Select
Else
If Target.Address = "$A$33" Then
Range("D33").Select
ActiveCell.FormulaR1C1 = "1"
Range("A33").Select
Else
If Target.Address = "$A$35" Then
Range("D35").Select
ActiveCell.FormulaR1C1 = "1"
Range("A35").Select
Else
If Target.Address = "$A$36" Then
Range("D36").Select
ActiveCell.FormulaR1C1 = "1"
Range("A36").Select
Else
If Target.Address = "$A$37" Then
Range("D37").Select
ActiveCell.FormulaR1C1 = "1"
Range("A37").Select
Else
If Target.Address = "$A$38" Then
Range("D38").Select
ActiveCell.FormulaR1C1 = "1"
Range("A38").Select
Else
If Target.Address = "$A$39" Then
Range("D39").Select
ActiveCell.FormulaR1C1 = "1"
Range("A39").Select
Else
If Target.Address = "$A$40" Then
Range("D40").Select
ActiveCell.FormulaR1C1 = "1"
Range("A40").Select
Else
If Target.Address = "$A$41" Then
Range("D41").Select
ActiveCell.FormulaR1C1 = "1"
Range("A41").Select
Else
If Target.Address = "$A$43" Then
Range("D43").Select
ActiveCell.FormulaR1C1 = "1"
Range("A43").Select
Else
If Target.Address = "$A$44" Then
Range("D44").Select
ActiveCell.FormulaR1C1 = "1"
Range("A44").Select
Else
If Target.Address = "$A$45" Then
Range("D45").Select
ActiveCell.FormulaR1C1 = "1"
Range("A45").Select
Else
If Target.Address = "$A$46" Then
Range("D46").Select
ActiveCell.FormulaR1C1 = "1"
Range("A46").Select
Else
If Target.Address = "$A$47" Then
Range("D47").Select
ActiveCell.FormulaR1C1 = "1"
Range("A47").Select
Else
If Target.Address = "$A$48" Then
Range("D48").Select
ActiveCell.FormulaR1C1 = "1"
Range("A48").Select
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Merci de votre aide à mon initiation.

 

[Staple1600]

Re : simplification macro vba

Bonsoir


En utilsant Select Case

edité: voir mon dernier message

 

[Staple1600]

Re : simplification macro vba

Re


Essaye cette modifiacation et dis nous si cela equivaut à ton premier code

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address(0, 0)
Case "A27", "A28", "A29", "A30", "A31", "A32", "A33", "A35", "A36", "A37", "A38", "A39", "A40", "A41", "A43", "A44", "A45", "A46", "A47", "A48"
Target.Offset(0, 4).Value = 1
Case "I26", "I34", "I42"
Target.Offset(2, 0) = 0
End Select
End Sub

 

[jluc56]

Re : simplification macro vba

oui impeccable, et plus simple .
encore merci