Demande de Rationalisation de code

[laduche81]

Bien le bonjour !

Mon code marche correctement, le problème est que j'ai pour le moment 300 lignes de code et que ca va monter bientôt à 2000 si je ne fais pas une simplification...
C'est très répétitif, je vous montre un extrait :

Sub Bouton5_Clic()

'MACRO Modif Date


REV = InputBox("Enter 1 for REV1, 2 for REV2, etc.", "REV Date Modification")

If REV = "" Then MsgBox "Canceled"

Selection.NumberFormat = "dd/mm/yy"

Mdir = InputBox("SELECT THE DATE: (dd/mm/yy)", "REV x")

If Mdir = "" Then
MsgBox "Canceled"

ElseIf REV = 1 And Range("A2") = 1 Then Sheets("WP data table").Range("C2").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 4 Then Sheets("WP data table").Range("C3").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 6 Then Sheets("WP data table").Range("C4").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 7 Then Sheets("WP data table").Range("C5").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 15 Then Sheets("WP data table").Range("C6").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 21 Then Sheets("WP data table").Range("C7").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 23 Then Sheets("WP data table").Range("C8").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 33 Then Sheets("WP data table").Range("C9").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 35 Then Sheets("WP data table").Range("C10").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 36 Then Sheets("WP data table").Range("C11").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 42 Then Sheets("WP data table").Range("C12").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 62 Then Sheets("WP data table").Range("C13").Value = CDate(Mdir)
ElseIf REV = 1 And Range("A2") = 63 Then Sheets("WP data table").Range("C14").Value = CDate(Mdir)

ElseIf REV = 2 And Range("A2") = 1 Then Sheets("WP data table").Range("E2").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 4 Then Sheets("WP data table").Range("E3").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 6 Then Sheets("WP data table").Range("E4").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 7 Then Sheets("WP data table").Range("E5").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 15 Then Sheets("WP data table").Range("E6").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 21 Then Sheets("WP data table").Range("E7").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 23 Then Sheets("WP data table").Range("E8").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 33 Then Sheets("WP data table").Range("E9").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 35 Then Sheets("WP data table").Range("E10").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 36 Then Sheets("WP data table").Range("E11").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 42 Then Sheets("WP data table").Range("E12").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 62 Then Sheets("WP data table").Range("E13").Value = CDate(Mdir)
ElseIf REV = 2 And Range("A2") = 63 Then Sheets("WP data table").Range("E14").Value = CDate(Mdir)

ElseIf REV = 3 And Range("A2") = 1 Then Sheets("WP data table").Range("G2").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 4 Then Sheets("WP data table").Range("G3").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 6 Then Sheets("WP data table").Range("G4").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 7 Then Sheets("WP data table").Range("G5").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 15 Then Sheets("WP data table").Range("G6").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 21 Then Sheets("WP data table").Range("G7").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 23 Then Sheets("WP data table").Range("G8").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 33 Then Sheets("WP data table").Range("G9").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 35 Then Sheets("WP data table").Range("G10").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 36 Then Sheets("WP data table").Range("G11").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 42 Then Sheets("WP data table").Range("G12").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 62 Then Sheets("WP data table").Range("G13").Value = CDate(Mdir)
ElseIf REV = 3 And Range("A2") = 63 Then Sheets("WP data table").Range("G14").Value = CDate(Mdir)

End If
End Sub

Voilà si quelqu'un a une idée pour simplifier la chose je suis preneur !

Merci beaucoup par avance !

Adrien.

 

[Guest]

Re : Demande de Rationalisation de code

Bonjour,

Cela pourrait-être quelque choses comme ça:

Sub Bouton5_Clic()
    Dim sCol As String
    rev = InputBox("Enter 1 for REV1, 2 for REV2, etc.", "REV Date Modification")
    If rev = "" Or Not IsNumeric(rev) Then
        MsgBox "Canceled"
        Exit Sub
    End If
    rev = CInt(rev)
    If rev > 0 And rev < 4 Then
        'Les indices de tableau commencent à 0
        sCol = Array("C", "D", "F")(rev - 1)
    Else
        MsgBox "Valeur hors limite"
        Exit Sub
    End If
    Selection.NumberFormat = "dd/mm/yy"
    mdir = InputBox("SELECT THE DATE: (dd/mm/yy)", "REV x")
    If Not IsDate(mdir) Then
        MsgBox "Canceled"
        Exit Sub
    End If
    Dim Adr As String
    Select Case Range("A2").Value
    Case 1: Adr = sCol & 2
    Case 4: Adr = sCol & 3
        '.......etc
    End Select
    If Adr <> "" Then Sheets("WP data table").Range(Adr) = CDate(mdir)
End Sub

Je te laisse le soin de compléter les 'Case X:

A+

 

[laduche81]

Re : Demande de Rationalisation de code

Merci beaucoup ! Je vais tester ! Bonne journée !

 

[Bebere]

Re : Demande de Rationalisation de code

bonjour Laduche,Hasco
une autre proposition avec en partie le code de Hasco
à tester

Sub Bouton5_Clic()
    Dim liste As Variant, l As Long
    'MACRO Modif Date

    liste = Array(1, 4, 6, 7, 15, 21, 23, 33, 35, 36, 42, 62, 63)

Rev = InputBox("Enter 1 for REV1, 2 for REV2, etc.", "REV Date Modification")
    If Rev = "" Or Not IsNumeric(Rev) Then
        MsgBox "Canceled"
        Exit Sub
    End If
    
    Rev = CInt(Rev)
    Selection.NumberFormat = "dd/mm/yy"
    mdir = InputBox("SELECT THE DATE: (dd/mm/yy)", "REV x")
    If Not IsDate(mdir) Then
        MsgBox "Canceled"
        Exit Sub
    End If
        For l = LBound(liste) To UBound(liste)
            Select Case Rev

            Case 1
                If Range("A2") = liste(l) Then Sheets("WP data table").Range("C" & l + 2).Value = CDate(mdir)

            Case 2
                If Range("A2") = liste(l) Then Sheets("WP data table").Range("E" & l + 2).Value = CDate(mdir)

            Case 3
                If Range("A2") = liste(l) Then Sheets("WP data table").Range("G" & l + 2).Value = CDate(mdir)

            End Select
        Next

End Sub

 

[laduche81]

Re : Demande de Rationalisation de code

Merci à tous les deux vos codes me simplifient la vie !

J'ai testé les deux et ils fonctionnent très bien, merci à nouveau et bonne journée !