Procedure too large

Bonjour,

Y'a til une idee pour eviter cet erreur "Procedure too large"

j'ai cree des drop down lists sur 10 cellules qui contiennent des codes et une fois on clique sur n'importe quel code de cette liste , on aura sa défintion dans la cellule juste en haut , ça fonctionne très bien pour les 10 premiéres cellules

mais je dois refaire la même chose pour d'autres cellules notons que c'est les mêmes codes et les mêmes définitions.malheureusement j'ai eu cet erreur qui me permettera pas de continuer

voici une petite aperçu de mon code , je voulais savoir si y'a til une maniére pour éviter quelques lignes de code

Merci

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) = "X4" Then

    Select Case Target
        Case "Educ": range1 = "B7"
        Case "EX1": range1 = "B25"
        Case "EX2": range1 = "B26"
        Case "EX3": range1 = "B27"
        Case "EX4": range1 = "B28"
        Case "EX5": range1 = "B29"
        Case "EX6": range1 = "B30"
        Case "EX7": range1 = "B31"
        Case "EX8": range1 = "B32"
        Case "EX9": range1 = "B33"
        Case "EX10": range1 = "B34"
        Case "A1": range1 = "B71"
        Case "A2": range1 = "B72"
        Case "PS1": range1 = "B73"
        Case "PS2": range1 = "B74"
        Case "AED1": range1 = "B45"
        Case "AED2": range1 = "B46"
     End Select
End If

If range1 <> "" Then
     Sheets("SBR").Range("X3") = _
                Sheets("Process Info").Range(range1).Value
End If

If Target.Address(0, 0) = "Y4" Then

    Select Case Target
        Case "Educ": range1a = "B7"
        Case "EX1": range1a = "B25"
        Case "EX2": range1a = "B26"
        Case "EX3": range1a = "B27"
        Case "EX4": range1a = "B28"
        Case "EX5": range1a = "B29"
        Case "EX6": range1a = "B30"
        Case "EX7": range1a = "B31"
        Case "EX8": range1a = "B32"
        Case "EX9": range1a = "B33"
        Case "EX10": range1a = "B34"
        Case "A1": range1a = "B71"
        Case "A2": range1a = "B72"
        Case "PS1": range1a = "B73"
        Case "PS2": range1a = "B74"
        Case "AED1": range1a = "B45"
         Case "AED2": range1a = "B46"
     End Select
End If

If range1a <> "" Then
     Sheets("SBR").Range("Y3") = _
                Sheets("Process Info").Range(range1a).Value
End If

If Target.Address(0, 0) = "Z4" Then

    Select Case Target
        Case "Educ": range1b = "B7"
        Case "EX1": range1b = "B25"
        Case "EX2": range1b = "B26"
        Case "EX3": range1b = "B27"
        Case "EX4": range1b = "B28"
        Case "EX5": range1b = "B29"
        Case "EX6": range1b = "B30"
        Case "EX7": range1b = "B31"
        Case "EX8": range1b = "B32"
        Case "EX9": range1b = "B33"
        Case "EX10": range1b = "B34"
        Case "A1": range1b = "B71"
        Case "A2": range1b = "B72"
        Case "PS1": range1b = "B73"
        Case "PS2": range1b = "B74"
        Case "AED1": range1b = "B45"
         Case "AED2": range1b = "B46"
     End Select
End If

If range1b <> "" Then
     Sheets("SBR").Range("Z3") = _
                Sheets("Process Info").Range(range1b).Value
End If

If Target.Address(0, 0) = "AA4" Then

    Select Case Target
        Case "Educ": range1c = "B7"
        Case "EX1": range1c = "B25"
        Case "EX2": range1c = "B26"
        Case "EX3": range1c = "B27"
        Case "EX4": range1c = "B28"
        Case "EX5": range1c = "B29"
        Case "EX6": range1c = "B30"
        Case "EX7": range1c = "B31"
        Case "EX8": range1c = "B32"
        Case "EX9": range1c = "B33"
        Case "EX10": range1c = "B34"
        Case "A1": range1c = "B71"
        Case "A2": range1c = "B72"
        Case "PS1": range1c = "B73"
        Case "PS2": range1c = "B74"
        Case "AED1": range1c = "B45"
         Case "AED2": range1c = "B46"
     End Select
End If

If range1c <> "" Then
     Sheets("SBR").Range("AA3") = _
                Sheets("Process Info").Range(range1c).Value
End If

If Target.Address(0, 0) = "AB4" Then

    Select Case Target
        Case "Educ": range1x = "B7"
        Case "EX1": range1x = "B25"
        Case "EX2": range1x = "B26"
        Case "EX3": range1x = "B27"
        Case "EX4": range1x = "B28"
        Case "EX5": range1x = "B29"
        Case "EX6": range1x = "B30"
        Case "EX7": range1x = "B31"
        Case "EX8": range1x = "B32"
        Case "EX9": range1x = "B33"
        Case "EX10": range1x = "B34"
        Case "A1": range1x = "B71"
        Case "A2": range1x = "B72"
        Case "PS1": range1x = "B73"
        Case "PS2": range1x = "B74"
        Case "AED1": range1x = "B45"
         Case "AED2": range1x = "B46"
     End Select
End If

If range1x <> "" Then
     Sheets("SBR").Range("AB3") = _
                Sheets("Process Info").Range(range1x).Value
End If

If Target.Address(0, 0) = "AB4" Then

    Select Case Target
        Case "Educ": range1z = "B7"
        Case "EX1": range1z = "B25"
        Case "EX2": range1z = "B26"
        Case "EX3": range1z = "B27"
        Case "EX4": range1z = "B28"
        Case "EX5": range1z = "B29"
        Case "EX6": range1z = "B30"
        Case "EX7": range1z = "B31"
        Case "EX8": range1z = "B32"
        Case "EX9": range1z = "B33"
        Case "EX10": range1z = "B34"
        Case "A1": range1z = "B71"
        Case "A2": range1z = "B72"
        Case "PS1": range1z = "B73"
        Case "PS2": range1z = "B74"
        Case "AED1": range1z = "B45"
         Case "AED2": range1z = "B46"
     End Select
End If

If range1z <> "" Then
     Sheets("SBR").Range("AB3") = _
                Sheets("Process Info").Range(range1z).Value
End If

If Target.Address(0, 0) = "AC4" Then

    Select Case Target
        Case "Educ": range1s = "B7"
        Case "EX1": range1s = "B25"
        Case "EX2": range1s = "B26"
        Case "EX3": range1s = "B27"
        Case "EX4": range1s = "B28"
        Case "EX5": range1s = "B29"
        Case "EX6": range1s = "B30"
        Case "EX7": range1s = "B31"
        Case "EX8": range1s = "B32"
        Case "EX9": range1s = "B33"
        Case "EX10": range1s = "B34"
        Case "A1": range1s = "B71"
        Case "A2": range1s = "B72"
        Case "PS1": range1s = "B73"
        Case "PS2": range1s = "B74"
        Case "AED1": range1s = "B45"
         Case "AED2": range1s = "B46"
     End Select
End If

If Target.Address(0, 0) = "BB4" Then

    Select Case Target

         Case "K1": sbr3 = "B49"
        Case "K2": sbr3 = "B50"
        Case "K3": sbr3 = "B51"
        Case "K4": sbr3 = "B52"
        Case "K5": sbr3 = "B53"
        Case "K6": sbr3 = "B54"
        Case "K7": sbr3 = "B55"
        Case "K8": sbr3 = "B56"
        Case "K9": sbr3 = "B57"
        Case "K10": sbr3 = "B58"
        Case "K11": sbr3 = "B59"
        Case "K12": sbr3 = "B60"
        Case "K13": sbr3 = "B61"
        Case "K14": sbr3 = "B62"
        Case "K15": sbr3 = "B63"
        Case "K16": sbr3 = "B64"
        Case "K17": sbr3 = "B65"
        Case "K18": sbr3 = "B66"
        Case "K19": sbr3 = "B67"
        Case "K20": sbr3 = "B68"
        Case "A1": sbr3 = "B71"
        Case "A2": sbr3 = "B72"
        Case "A3": sbr3 = "B73"
        Case "A4": sbr3 = "B74"
        Case "A5": sbr3 = "B75"
        Case "A6": sbr3 = "B76"
        Case "A7": sbr3 = "B77"
        Case "A8": sbr3 = "B78"
        Case "A9": sbr3 = "B79"
        Case "A10": sbr3 = "B80"
        Case "PS1": sbr3 = "B83"
        Case "PS2": sbr3 = "B84"
        Case "PS3": sbr3 = "B85"
        Case "PS4": sbr3 = "B86"
        Case "PS5": sbr3 = "B87"
        Case "PS6": sbr3 = "B88"
        Case "PS7": sbr3 = "B89"
        Case "PS8": sbr3 = "B90"
        Case "PS9": sbr3 = "B91"
        Case "PS10": sbr3 = "B92"

        Case "Cmp1": sbr3 = "B95"
        Case "Cmp2": sbr3 = "B96"
        Case "Cmp3": sbr3 = "B97"
        Case "Cmp4": sbr3 = "B98"
        Case "Cmp5": sbr3 = "B99"
        Case "Cmp6": sbr3 = "B100"
        Case "Cmp7": sbr3 = "B101"
        Case "Cmp8": sbr3 = "B102"
        Case "Cmp9": sbr3 = "B103"
        Case "Cmp10": sbr3 = "B104"
        Case "Cmp11": sbr3 = "B107"
        Case "Cmp12": sbr3 = "B108"
        Case "Cmp13": sbr3 = "B109"
        Case "Cmp14": sbr3 = "B110"
        Case "Cmp15": sbr3 = "B111"

     End Select
End If

If sbr3 <> "" Then
     Sheets("SBR").Range("BA3") = _
                Sheets("Process Info").Range(sbr3).Value
End If

If Target.Address(0, 0) = "BD4" Then

    Select Case Target
      Case "K1": sbr4 = "B49"
        Case "K2": sbr4 = "B50"
        Case "K3": sbr4 = "B51"
        Case "K4": sbr4 = "B52"
        Case "K5": sbr4 = "B53"
        Case "K6": sbr4 = "B54"
        Case "K7": sbr4 = "B55"
        Case "K8": sbr4 = "B56"
        Case "K9": sbr4 = "B57"
        Case "K10": sbr4 = "B58"
        Case "K11": sbr4 = "B59"
        Case "K12": sbr4 = "B60"
        Case "K13": sbr4 = "B61"
        Case "K14": sbr4 = "B62"
        Case "K15": sbr4 = "B63"
        Case "K16": sbr4 = "B64"
        Case "K17": sbr4 = "B65"
        Case "K18": sbr4 = "B66"
        Case "K19": sbr4 = "B67"
        Case "K20": sbr4 = "B68"
        Case "A1": sbr4 = "B71"
        Case "A2": sbr4 = "B72"
        Case "A3": sbr4 = "B73"
        Case "A4": sbr4 = "B74"
        Case "A5": sbr4 = "B75"
        Case "A6": sbr4 = "B76"
        Case "A7": sbr4 = "B77"
        Case "A8": sbr4 = "B78"
        Case "A9": sbr4 = "B79"
        Case "A10": sbr4 = "B80"
        Case "PS1": sbr4 = "B83"
        Case "PS2": sbr4 = "B84"
        Case "PS3": sbr4 = "B85"
        Case "PS4": sbr4 = "B86"
        Case "PS5": sbr4 = "B87"
        Case "PS6": sbr4 = "B88"
        Case "PS7": sbr4 = "B89"
        Case "PS8": sbr4 = "B90"
        Case "PS9": sbr4 = "B91"
        Case "PS10": sbr4 = "B92"

        Case "Cmp1": sbr4 = "B95"
        Case "Cmp2": sbr4 = "B96"
        Case "Cmp3": sbr4 = "B97"
        Case "Cmp4": sbr4 = "B98"
        Case "Cmp5": sbr4 = "B99"
        Case "Cmp6": sbr4 = "B100"
        Case "Cmp7": sbr4 = "B101"
        Case "Cmp8": sbr4 = "B102"
        Case "Cmp9": sbr4 = "B103"
        Case "Cmp10": sbr4 = "B104"
        Case "Cmp11": sbr4 = "B107"
        Case "Cmp12": sbr4 = "B108"
        Case "Cmp13": sbr4 = "B109"
        Case "Cmp14": sbr4 = "B110"
        Case "Cmp15": sbr4 = "B111"

     End Select
End If

If sbr4 <> "" Then
     Sheets("SBR").Range("BC3") = _
                Sheets("Process Info").Range(sbr4).Value
End If

If Target.Address(0, 0) = "BF4" Then

    Select Case Target
 Case "K1": sbr2 = "B49"
        Case "K2": sbR5 = "B50"
        Case "K3": sbR5 = "B51"
        Case "K4": sbR5 = "B52"
        Case "K5": sbR5 = "B53"
        Case "K6": sbR5 = "B54"
        Case "K7": sbR5 = "B55"
        Case "K8": sbR5 = "B56"
        Case "K9": sbR5 = "B57"
        Case "K10": sbR5 = "B58"
        Case "K11": sbR5 = "B59"
        Case "K12": sbR5 = "B60"
        Case "K13": sbR5 = "B61"
        Case "K14": sbR5 = "B62"
        Case "K15": sbR5 = "B63"
        Case "K16": sbR5 = "B64"
        Case "K17": sbR5 = "B65"
        Case "K18": sbR5 = "B66"
        Case "K19": sbR5 = "B67"
        Case "K20": sbR5 = "B68"
        Case "A1": sbR5 = "B71"
        Case "A2": sbR5 = "B72"
        Case "A3": sbR5 = "B73"
        Case "A4": sbR5 = "B74"
        Case "A5": sbR5 = "B75"
        Case "A6": sbR5 = "B76"
        Case "A7": sbR5 = "B77"
        Case "A8": sbR5 = "B78"
        Case "A9": sbR5 = "B79"
        Case "A10": sbR5 = "B80"
        Case "PS1": sbR5 = "B83"
        Case "PS2": sbR5 = "B84"
        Case "PS3": sbR5 = "B85"
        Case "PS4": sbR5 = "B86"
        Case "PS5": sbR5 = "B87"
        Case "PS6": sbR5 = "B88"
        Case "PS7": sbR5 = "B89"
        Case "PS8": sbR5 = "B90"
        Case "PS9": sbR5 = "B91"
        Case "PS10": sbR5 = "B92"

        Case "Cmp1": sbR5 = "B95"
        Case "Cmp2": sbR5 = "B96"
        Case "Cmp3": sbR5 = "B97"
        Case "Cmp4": sbR5 = "B98"
        Case "Cmp5":  sbR5 = "B99"
        Case "Cmp6":  sbR5 = "B100"
        Case "Cmp7":  sbR5 = "B101"
        Case "Cmp8":  sbR5 = "B102"
        Case "Cmp9":  sbR5 = "B103"
        Case "Cmp10": sbR5 = "B104"
        Case "Cmp11": sbR5 = "B107"
        Case "Cmp12": sbR5 = "B108"
        Case "Cmp13": sbR5 = "B109"
        Case "Cmp14": sbR5 = "B110"
        Case "Cmp15": sbR5 = "B111"

     End Select
End If

If sbR5 <> "" Then
     Sheets("SBR").Range("BE3") = _
                Sheets("Process Info").Range(sbR5).Value
End If

If Target.Address(0, 0) = "BH4" Then

    Select Case Target
      Case "K1": sbr6 = "B49"
        Case "K2": sbr6 = "B50"
        Case "K3": sbr6 = "B51"
        Case "K4": sbr6 = "B52"
        Case "K5": sbr6 = "B53"
        Case "K6": sbr6 = "B54"
        Case "K7": sbr6 = "B55"
        Case "K8": sbr6 = "B56"
        Case "K9": sbr6 = "B57"
        Case "K10": sbr6 = "B58"
        Case "K11": sbr6 = "B59"
        Case "K12": sbr6 = "B60"
        Case "K13": sbr6 = "B61"
        Case "K14": sbr6 = "B62"
        Case "K15": sbr6 = "B63"
        Case "K16": sbr6 = "B64"
        Case "K17": sbr6 = "B65"
        Case "K18": sbr6 = "B66"
        Case "K19": sbr6 = "B67"
        Case "K20": sbr6 = "B68"
        Case "A1": sbr6 = "B71"
        Case "A2": sbr6 = "B72"
        Case "A3": sbr6 = "B73"
        Case "A4": sbr6 = "B74"
        Case "A5": sbr6 = "B75"
        Case "A6": sbr6 = "B76"
        Case "A7": sbr6 = "B77"
        Case "A8": sbr6 = "B78"
        Case "A9": sbr6 = "B79"
        Case "A10": sbr6 = "B80"
        Case "PS1": sbr6 = "B83"
        Case "PS2": sbr6 = "B84"
        Case "PS3": sbr6 = "B85"
        Case "PS4": sbr6 = "B86"
        Case "PS5": sbr6 = "B87"
        Case "PS6": sbr6 = "B88"
        Case "PS7": sbr6 = "B89"
        Case "PS8": sbr6 = "B90"
        Case "PS9": sbr6 = "B91"
        Case "PS10": sbr6 = "B92"

        Case "Cmp1": sbr6 = "B95"
        Case "Cmp2": sbr6 = "B96"
        Case "Cmp3": sbr6 = "B97"
        Case "Cmp4": sbr6 = "B98"
        Case "Cmp5": sbr6 = "B99"
        Case "Cmp6": sbr6 = "B100"
        Case "Cmp7": sbr6 = "B101"
        Case "Cmp8": sbr6 = "B102"
        Case "Cmp9": sbr6 = "B103"
        Case "Cmp10": sbr6 = "B104"
        Case "Cmp11": sbr6 = "B107"
        Case "Cmp12": sbr6 = "B108"
        Case "Cmp13": sbr6 = "B109"
        Case "Cmp14": sbr6 = "B110"
        Case "Cmp15": sbr6 = "B111"

     End Select
End If

Bonjour,

Ouf ! Tu t'es dispensée d'un travail de réflexion avant ! Maintenant, tu n'y coupes plus !

Cordialement.

Je t'ai pas obligé de me répondre.

Et alors ! Aurais-je contesté ta liberté de poser une question ?

De toute façon ce n'était ce n'était juste qu'un constat ! L'aspect positif du problème est que cela devrait inciter à programmer de façon un peu plus modulaire d'une part et à rechercher des algorithmes propres à réduire sensiblement le volume de code pour un même résultat. A progresser donc !

Je ne donne certes pas de solution tout faite mais l'orientation est bien la bonne.

Cordialement.

Rechercher des sujets similaires à "procedure too large"