Raccourcir une procédure

Le 17/01/2018 à 23:04

Bonjour,

je suis vraiment novice en VBA sur excell, je vient de crée un userform et malheureusement j'ai une de mes procédure qui est beaucoup trop longue. je cherche une solution pour crée des modules ou des fonctions qui me permettrait de simplifier mon code. voici une partie de mon code si quelqu'un peux m'aide çà serait vraiment geniale :

If Combosemaine = "1" And Combojour = "Lundi" Then ' semaine 1 lundi

Worksheets("S01").Range("D4").Value = TextRecette.Value

Worksheets("S01").Range("D5").Value = TextBancontact.Value

Worksheets("S01").Range("D6").Value = TextSodexo.Value

Worksheets("S01").Range("D8").Value = TextBonQuali.Value

Worksheets("S01").Range("C11").Value = Textnomdiver1.Value

Worksheets("S01").Range("C12").Value = Textnomdiver2.Value

Worksheets("S01").Range("D11").Value = TextDiver1.Value

Worksheets("S01").Range("D12").Value = TextDiver2.Value

Worksheets("S01").Range("D22").Value = Textsolde.Value

Worksheets("S01").Range("C23").Value = Text500M.Value

Worksheets("S01").Range("C24").Value = Text200M.Value

Worksheets("S01").Range("C25").Value = Text100M.Value

Worksheets("S01").Range("C26").Value = Text50M.Value

Worksheets("S01").Range("C27").Value = Text20M.Value

Worksheets("S01").Range("C28").Value = Text10M.Value

Worksheets("S01").Range("C29").Value = Text5M.Value

Worksheets("S01").Range("B30").Value = Text2M.Value

Worksheets("S01").Range("B31").Value = Text1M.Value

Worksheets("S01").Range("B32").Value = Text05M.Value

Worksheets("S01").Range("B33").Value = Text02M.Value

Worksheets("S01").Range("B34").Value = Text01M.Value

Worksheets("S01").Range("B35").Value = Text005M.Value

Worksheets("S01").Range("B36").Value = Text002M.Value

Worksheets("S01").Range("C44").Value = Text500B.Value

Worksheets("S01").Range("C45").Value = Text200B.Value

Worksheets("S01").Range("C46").Value = Text100B.Value

Worksheets("S01").Range("C47").Value = Text50B.Value

Worksheets("S01").Range("C48").Value = Text20B.Value

Worksheets("S01").Range("C49").Value = Text10B.Value

Worksheets("S01").Range("C50").Value = Text5B.Value

Worksheets("S01").Range("A15").Value = Comboperso1B.Value

Worksheets("S01").Range("A16").Value = Comboperso2B.Value

Worksheets("S01").Range("A17").Value = Comboperso3B.Value

Worksheets("S01").Range("A18").Value = Comboperso4B.Value

Worksheets("S01").Range("A19").Value = Comboperso5B.Value

Worksheets("S01").Range("C15").Value = Textperso1B.Value

Worksheets("S01").Range("C16").Value = Textperso2B.Value

Worksheets("S01").Range("C17").Value = Textperso3B.Value

Worksheets("S01").Range("C18").Value = Textperso4B.Value

Worksheets("S01").Range("C19").Value = Textperso5B.Value

Else

If Combosemaine = "1" And Combojour = "Mardi" Then ' semaine 1 Mardi

Worksheets("S01").Range("H4").Value = TextRecette.Value

Worksheets("S01").Range("H5").Value = TextBancontact.Value

Worksheets("S01").Range("H6").Value = TextSodexo.Value

Worksheets("S01").Range("H8").Value = TextBonQuali.Value

Worksheets("S01").Range("G11").Value = Textnomdiver1.Value

Worksheets("S01").Range("G12").Value = Textnomdiver2.Value

Worksheets("S01").Range("H11").Value = TextDiver1.Value

Worksheets("S01").Range("H12").Value = TextDiver2.Value

Worksheets("S01").Range("H22").Value = Textsolde.Value

Worksheets("S01").Range("H23").Value = Text500M.Value

Worksheets("S01").Range("H24").Value = Text200M.Value

Worksheets("S01").Range("H25").Value = Text100M.Value

Worksheets("S01").Range("H26").Value = Text50M.Value

Worksheets("S01").Range("H27").Value = Text20M.Value

Worksheets("S01").Range("H28").Value = Text10M.Value

Worksheets("S01").Range("H29").Value = Text5M.Value

Worksheets("S01").Range("F30").Value = Text2M.Value

Worksheets("S01").Range("F31").Value = Text1M.Value

Worksheets("S01").Range("F32").Value = Text05M.Value

Worksheets("S01").Range("F33").Value = Text02M.Value

Worksheets("S01").Range("F34").Value = Text01M.Value

Worksheets("S01").Range("F35").Value = Text005M.Value

Worksheets("S01").Range("F36").Value = Text002M.Value

Worksheets("S01").Range("G44").Value = Text500B.Value

Worksheets("S01").Range("G45").Value = Text200B.Value

Worksheets("S01").Range("G46").Value = Text100B.Value

Worksheets("S01").Range("G47").Value = Text50B.Value

Worksheets("S01").Range("G48").Value = Text20B.Value

Worksheets("S01").Range("G49").Value = Text10B.Value

Worksheets("S01").Range("G50").Value = Text5B.Value

Worksheets("S01").Range("E15").Value = Comboperso1B.Value

Worksheets("S01").Range("E16").Value = Comboperso2B.Value

Worksheets("S01").Range("E17").Value = Comboperso3B.Value

Worksheets("S01").Range("E18").Value = Comboperso4B.Value

Worksheets("S01").Range("E19").Value = Comboperso5B.Value

Worksheets("S01").Range("G15").Value = Textperso1B.Value

Worksheets("S01").Range("G16").Value = Textperso2B.Value

Worksheets("S01").Range("G17").Value = Textperso3B.Value

Worksheets("S01").Range("G18").Value = Textperso4B.Value

Worksheets("S01").Range("G19").Value = Textperso5B.Value

Else

If Combosemaine = "1" And Combojour = "Mercredi" Then

Worksheets("S01").Range("L4").Value = TextRecette.Value

Worksheets("S01").Range("L5").Value = TextBancontact.Value

Worksheets("S01").Range("L6").Value = TextSodexo.Value

Worksheets("S01").Range("L8").Value = TextBonQuali.Value

Worksheets("S01").Range("K11").Value = Textnomdiver1.Value

Worksheets("S01").Range("K12").Value = Textnomdiver2.Value

Worksheets("S01").Range("L11").Value = TextDiver1.Value

Worksheets("S01").Range("L12").Value = TextDiver2.Value

Worksheets("S01").Range("L22").Value = Textsolde.Value

Worksheets("S01").Range("K23").Value = Text500M.Value

Worksheets("S01").Range("K24").Value = Text200M.Value

Worksheets("S01").Range("K25").Value = Text100M.Value

Worksheets("S01").Range("K26").Value = Text50M.Value

Worksheets("S01").Range("K27").Value = Text20M.Value

Worksheets("S01").Range("K28").Value = Text10M.Value

Worksheets("S01").Range("K29").Value = Text5M.Value

Worksheets("S01").Range("J30").Value = Text2M.Value

Worksheets("S01").Range("J31").Value = Text1M.Value

Worksheets("S01").Range("J32").Value = Text05M.Value

Worksheets("S01").Range("J33").Value = Text02M.Value

Worksheets("S01").Range("J34").Value = Text01M.Value

Worksheets("S01").Range("J35").Value = Text005M.Value

Worksheets("S01").Range("J36").Value = Text002M.Value

Worksheets("S01").Range("K44").Value = Text500B.Value

Worksheets("S01").Range("K45").Value = Text200B.Value

Worksheets("S01").Range("K46").Value = Text100B.Value

Worksheets("S01").Range("K47").Value = Text50B.Value

Worksheets("S01").Range("K48").Value = Text20B.Value

Worksheets("S01").Range("K49").Value = Text10B.Value

Worksheets("S01").Range("K50").Value = Text5B.Value

Worksheets("S01").Range("I15").Value = Comboperso1B.Value

Worksheets("S01").Range("I16").Value = Comboperso2B.Value

Worksheets("S01").Range("I17").Value = Comboperso3B.Value

Worksheets("S01").Range("I18").Value = Comboperso4B.Value

Worksheets("S01").Range("I19").Value = Comboperso5B.Value

Worksheets("S01").Range("K15").Value = Textperso1B.Value

Worksheets("S01").Range("K16").Value = Textperso2B.Value

Worksheets("S01").Range("K17").Value = Textperso3B.Value

Worksheets("S01").Range("K18").Value = Textperso4B.Value

Worksheets("S01").Range("K19").Value = Textperso5B.Value

Else

If Combosemaine = "1" And Combojour = "Jeudi" Then

Worksheets("S01").Range("P4").Value = TextRecette.Value

Worksheets("S01").Range("P5").Value = TextBancontact.Value

Worksheets("S01").Range("P6").Value = TextSodexo.Value

Worksheets("S01").Range("P8").Value = TextBonQuali.Value

Worksheets("S01").Range("K11").Value = Textnomdiver1.Value

Worksheets("S01").Range("K12").Value = Textnomdiver2.Value

Worksheets("S01").Range("L11").Value = TextDiver1.Value

Worksheets("S01").Range("L12").Value = TextDiver2.Value

Worksheets("S01").Range("L22").Value = Textsolde.Value

Worksheets("S01").Range("K23").Value = Text500M.Value

Worksheets("S01").Range("K24").Value = Text200M.Value

Worksheets("S01").Range("K25").Value = Text100M.Value

Worksheets("S01").Range("K26").Value = Text50M.Value

Worksheets("S01").Range("K27").Value = Text20M.Value

Worksheets("S01").Range("K28").Value = Text10M.Value

Worksheets("S01").Range("K29").Value = Text5M.Value

Worksheets("S01").Range("J30").Value = Text2M.Value

Worksheets("S01").Range("J31").Value = Text1M.Value

Worksheets("S01").Range("J32").Value = Text05M.Value

Worksheets("S01").Range("J33").Value = Text02M.Value

Worksheets("S01").Range("J34").Value = Text01M.Value

Worksheets("S01").Range("J35").Value = Text005M.Value

Worksheets("S01").Range("J36").Value = Text002M.Value

Worksheets("S01").Range("K44").Value = Text500B.Value

Worksheets("S01").Range("K45").Value = Text200B.Value

Worksheets("S01").Range("K46").Value = Text100B.Value

Worksheets("S01").Range("K47").Value = Text50B.Value

Worksheets("S01").Range("K48").Value = Text20B.Value

Worksheets("S01").Range("K49").Value = Text10B.Value

Worksheets("S01").Range("K50").Value = Text5B.Value

Worksheets("S01").Range("I15").Value = Comboperso1B.Value

Worksheets("S01").Range("I16").Value = Comboperso2B.Value

Worksheets("S01").Range("I17").Value = Comboperso3B.Value

Worksheets("S01").Range("I18").Value = Comboperso4B.Value

Worksheets("S01").Range("I19").Value = Comboperso5B.Value

Worksheets("S01").Range("K15").Value = Textperso1B.Value

Worksheets("S01").Range("K16").Value = Textperso2B.Value

Worksheets("S01").Range("K17").Value = Textperso3B.Value

Worksheets("S01").Range("K18").Value = Textperso4B.Value

Worksheets("S01").Range("K19").Value = Textperso5B.Value

Else

If Combosemaine = "1" And Combojour = "Vendredi" Then

Worksheets("S01").Range("T4").Value = TextRecette.Value

Worksheets("S01").Range("T5").Value = TextBancontact.Value

Worksheets("S01").Range("T6").Value = TextSodexo.Value

Worksheets("S01").Range("T8").Value = TextBonQuali.Value

Worksheets("S01").Range("S11").Value = Textnomdiver1.Value

Worksheets("S01").Range("S12").Value = Textnomdiver2.Value

Worksheets("S01").Range("T11").Value = TextDiver1.Value

Worksheets("S01").Range("T12").Value = TextDiver2.Value

Worksheets("S01").Range("T22").Value = Textsolde.Value

Worksheets("S01").Range("S23").Value = Text500M.Value

Worksheets("S01").Range("S24").Value = Text200M.Value

Worksheets("S01").Range("S25").Value = Text100M.Value

Worksheets("S01").Range("S26").Value = Text50M.Value

Worksheets("S01").Range("S27").Value = Text20M.Value

Worksheets("S01").Range("S28").Value = Text10M.Value

Worksheets("S01").Range("S29").Value = Text5M.Value

Worksheets("S01").Range("R30").Value = Text2M.Value

Worksheets("S01").Range("R31").Value = Text1M.Value

Worksheets("S01").Range("R32").Value = Text05M.Value

Worksheets("S01").Range("R33").Value = Text02M.Value

Worksheets("S01").Range("R34").Value = Text01M.Value

Worksheets("S01").Range("R35").Value = Text005M.Value

Worksheets("S01").Range("R36").Value = Text002M.Value

Worksheets("S01").Range("S44").Value = Text500B.Value

Worksheets("S01").Range("S45").Value = Text200B.Value

Worksheets("S01").Range("S46").Value = Text100B.Value

Worksheets("S01").Range("S47").Value = Text50B.Value

Worksheets("S01").Range("S48").Value = Text20B.Value

Worksheets("S01").Range("S49").Value = Text10B.Value

Worksheets("S01").Range("S50").Value = Text5B.Value

Worksheets("S01").Range("Q15").Value = Comboperso1B.Value

Worksheets("S01").Range("Q16").Value = Comboperso2B.Value

Worksheets("S01").Range("Q17").Value = Comboperso3B.Value

Worksheets("S01").Range("Q18").Value = Comboperso4B.Value

Worksheets("S01").Range("Q19").Value = Comboperso5B.Value

Worksheets("S01").Range("S15").Value = Textperso1B.Value

Worksheets("S01").Range("S16").Value = Textperso2B.Value

Worksheets("S01").Range("S17").Value = Textperso3B.Value

Worksheets("S01").Range("S18").Value = Textperso4B.Value

Worksheets("S01").Range("S19").Value = Textperso5B.Value

Else

If Combosemaine = "1" And Combojour = "Samedi" Then

Worksheets("S01").Range("X4").Value = TextRecette.Value

Worksheets("S01").Range("X5").Value = TextBancontact.Value

Worksheets("S01").Range("X6").Value = TextSodexo.Value

Worksheets("S01").Range("X8").Value = TextBonQuali.Value

Worksheets("S01").Range("W11").Value = Textnomdiver1.Value

Worksheets("S01").Range("W12").Value = Textnomdiver2.Value

Worksheets("S01").Range("X11").Value = TextDiver1.Value

Worksheets("S01").Range("X12").Value = TextDiver2.Value

Worksheets("S01").Range("X22").Value = Textsolde.Value

Worksheets("S01").Range("W23").Value = Text500M.Value

Worksheets("S01").Range("W24").Value = Text200M.Value

Worksheets("S01").Range("W25").Value = Text100M.Value

Worksheets("S01").Range("W26").Value = Text50M.Value

Worksheets("S01").Range("W27").Value = Text20M.Value

Worksheets("S01").Range("W28").Value = Text10M.Value

Worksheets("S01").Range("W29").Value = Text5M.Value

Worksheets("S01").Range("V30").Value = Text2M.Value

Worksheets("S01").Range("V31").Value = Text1M.Value

Worksheets("S01").Range("V32").Value = Text05M.Value

Worksheets("S01").Range("V33").Value = Text02M.Value

Worksheets("S01").Range("V34").Value = Text01M.Value

Worksheets("S01").Range("V35").Value = Text005M.Value

Worksheets("S01").Range("V36").Value = Text002M.Value

Worksheets("S01").Range("W44").Value = Text500B.Value

Worksheets("S01").Range("W45").Value = Text200B.Value

Worksheets("S01").Range("W46").Value = Text100B.Value

Worksheets("S01").Range("W47").Value = Text50B.Value

Worksheets("S01").Range("W48").Value = Text20B.Value

Worksheets("S01").Range("W49").Value = Text10B.Value

Worksheets("S01").Range("W50").Value = Text5B.Value

Worksheets("S01").Range("U15").Value = Comboperso1B.Value

Worksheets("S01").Range("U16").Value = Comboperso2B.Value

Worksheets("S01").Range("U17").Value = Comboperso3B.Value

Worksheets("S01").Range("U18").Value = Comboperso4B.Value

Worksheets("S01").Range("U19").Value = Comboperso5B.Value

Worksheets("S01").Range("W15").Value = Textperso1B.Value

Worksheets("S01").Range("W16").Value = Textperso2B.Value

Worksheets("S01").Range("W17").Value = Textperso3B.Value

Worksheets("S01").Range("W18").Value = Textperso4B.Value

Worksheets("S01").Range("W19").Value = Textperso5B.Value

Else ' semaine 2

If Combosemaine = "2" And Combojour = "Lundi" Then

Worksheets("S02").Range("D4").Value = TextRecette.Value

Worksheets("S02").Range("D5").Value = TextBancontact.Value

Worksheets("S02").Range("D6").Value = TextSodexo.Value

Worksheets("S02").Range("D8").Value = TextBonQuali.Value

Worksheets("S02").Range("C11").Value = Textnomdiver1.Value

Worksheets("S02").Range("C12").Value = Textnomdiver2.Value

Worksheets("S02").Range("D11").Value = TextDiver1.Value

Worksheets("S02").Range("D12").Value = TextDiver2.Value

Worksheets("S02").Range("D22").Value = Textsolde.Value

Worksheets("S02").Range("C23").Value = Text500M.Value

Worksheets("S02").Range("C24").Value = Text200M.Value

Worksheets("S02").Range("C25").Value = Text100M.Value

Worksheets("S02").Range("C26").Value = Text50M.Value

Worksheets("S02").Range("C27").Value = Text20M.Value

Worksheets("S02").Range("C28").Value = Text10M.Value

Worksheets("S02").Range("C29").Value = Text5M.Value

Worksheets("S02").Range("B30").Value = Text2M.Value

Worksheets("S02").Range("B31").Value = Text1M.Value

Worksheets("S02").Range("B32").Value = Text05M.Value

Worksheets("S02").Range("B33").Value = Text02M.Value

Worksheets("S02").Range("B34").Value = Text01M.Value

Worksheets("S02").Range("B35").Value = Text005M.Value

Worksheets("S02").Range("B36").Value = Text002M.Value

Worksheets("S02").Range("C44").Value = Text500B.Value

Worksheets("S02").Range("C45").Value = Text200B.Value

Worksheets("S02").Range("C46").Value = Text100B.Value

Worksheets("S02").Range("C47").Value = Text50B.Value

Worksheets("S02").Range("C48").Value = Text20B.Value

Worksheets("S02").Range("C49").Value = Text10B.Value

Worksheets("S02").Range("C50").Value = Text5B.Value

Worksheets("S02").Range("A15").Value = Comboperso1B.Value

Worksheets("S02").Range("A16").Value = Comboperso2B.Value

Worksheets("S02").Range("A17").Value = Comboperso3B.Value

Worksheets("S02").Range("A18").Value = Comboperso4B.Value

Worksheets("S02").Range("A19").Value = Comboperso5B.Value

Worksheets("S02").Range("C15").Value = Textperso1B.Value

Worksheets("S02").Range("C16").Value = Textperso2B.Value

Worksheets("S02").Range("C17").Value = Textperso3B.Value

Worksheets("S02").Range("C18").Value = Textperso4B.Value

Worksheets("S02").Range("C19").Value = Textperso5B.Value

Else

If Combosemaine = "2" And Combojour = "Mardi" Then

Worksheets("S02").Range("H4").Value = TextRecette.Value

Worksheets("S02").Range("H5").Value = TextBancontact.Value

Worksheets("S02").Range("H6").Value = TextSodexo.Value

Worksheets("S02").Range("H8").Value = TextBonQuali.Value

Worksheets("S02").Range("G11").Value = Textnomdiver1.Value

Worksheets("S02").Range("G12").Value = Textnomdiver2.Value

Worksheets("S02").Range("H11").Value = TextDiver1.Value

Worksheets("S02").Range("H12").Value = TextDiver2.Value

Worksheets("S02").Range("H22").Value = Textsolde.Value

Worksheets("S02").Range("H23").Value = Text500M.Value

Worksheets("S02").Range("H24").Value = Text200M.Value

Worksheets("S02").Range("H25").Value = Text100M.Value

Worksheets("S02").Range("H26").Value = Text50M.Value

Worksheets("S02").Range("H27").Value = Text20M.Value

Worksheets("S02").Range("H28").Value = Text10M.Value

Worksheets("S02").Range("H29").Value = Text5M.Value

Worksheets("S02").Range("F30").Value = Text2M.Value

Worksheets("S02").Range("F31").Value = Text1M.Value

Worksheets("S02").Range("F32").Value = Text05M.Value

Worksheets("S02").Range("F33").Value = Text02M.Value

Worksheets("S02").Range("F34").Value = Text01M.Value

Worksheets("S02").Range("F35").Value = Text005M.Value

Worksheets("S02").Range("F36").Value = Text002M.Value

Worksheets("S02").Range("G44").Value = Text500B.Value

Worksheets("S02").Range("G45").Value = Text200B.Value

Worksheets("S02").Range("G46").Value = Text100B.Value

Worksheets("S02").Range("G47").Value = Text50B.Value

Worksheets("S02").Range("G48").Value = Text20B.Value

Worksheets("S02").Range("G49").Value = Text10B.Value

Worksheets("S02").Range("G50").Value = Text5B.Value

Worksheets("S02").Range("E15").Value = Comboperso1B.Value

Worksheets("S02").Range("E16").Value = Comboperso2B.Value

Worksheets("S02").Range("E17").Value = Comboperso3B.Value

Worksheets("S02").Range("E18").Value = Comboperso4B.Value

Worksheets("S02").Range("E19").Value = Comboperso5B.Value

Worksheets("S02").Range("G15").Value = Textperso1B.Value

Worksheets("S02").Range("G16").Value = Textperso2B.Value

Worksheets("S02").Range("G17").Value = Textperso3B.Value

Worksheets("S02").Range("G18").Value = Textperso4B.Value

Worksheets("S02").Range("G19").Value = Textperso5B.Value

Else

If Combosemaine = "2" And Combojour = "Mercredi" Then

Worksheets("S02").Range("L4").Value = TextRecette.Value

Worksheets("S02").Range("L5").Value = TextBancontact.Value

Worksheets("S02").Range("L6").Value = TextSodexo.Value

Worksheets("S02").Range("L8").Value = TextBonQuali.Value

Worksheets("S02").Range("K11").Value = Textnomdiver1.Value

Worksheets("S02").Range("K12").Value = Textnomdiver2.Value

Worksheets("S02").Range("L11").Value = TextDiver1.Value

Worksheets("S02").Range("L12").Value = TextDiver2.Value

Worksheets("S02").Range("L22").Value = Textsolde.Value

Worksheets("S02").Range("K23").Value = Text500M.Value

Worksheets("S02").Range("K24").Value = Text200M.Value

Worksheets("S02").Range("K25").Value = Text100M.Value

Worksheets("S02").Range("K26").Value = Text50M.Value

Worksheets("S02").Range("K27").Value = Text20M.Value

Worksheets("S02").Range("K28").Value = Text10M.Value

Worksheets("S02").Range("K29").Value = Text5M.Value

Worksheets("S02").Range("J30").Value = Text2M.Value

Worksheets("S02").Range("J31").Value = Text1M.Value

Worksheets("S02").Range("J32").Value = Text05M.Value

Worksheets("S02").Range("J33").Value = Text02M.Value

Worksheets("S02").Range("J34").Value = Text01M.Value

Worksheets("S02").Range("J35").Value = Text005M.Value

Worksheets("S02").Range("J36").Value = Text002M.Value

Worksheets("S02").Range("K44").Value = Text500B.Value

Worksheets("S02").Range("K45").Value = Text200B.Value

Worksheets("S02").Range("K46").Value = Text100B.Value

Worksheets("S02").Range("K47").Value = Text50B.Value

Worksheets("S02").Range("K48").Value = Text20B.Value

Worksheets("S02").Range("K49").Value = Text10B.Value

Worksheets("S02").Range("K50").Value = Text5B.Value

Worksheets("S02").Range("I15").Value = Comboperso1B.Value

Worksheets("S02").Range("I16").Value = Comboperso2B.Value

Worksheets("S02").Range("I17").Value = Comboperso3B.Value

Worksheets("S02").Range("I18").Value = Comboperso4B.Value

Worksheets("S02").Range("I19").Value = Comboperso5B.Value

Worksheets("S02").Range("K15").Value = Textperso1B.Value

Worksheets("S02").Range("K16").Value = Textperso2B.Value

Worksheets("S02").Range("K17").Value = Textperso3B.Value

Worksheets("S02").Range("K18").Value = Textperso4B.Value

Worksheets("S02").Range("K19").Value = Textperso5B.Value

Else

If Combosemaine = "2" And Combojour = "Jeudi" Then

Worksheets("S02").Range("P4").Value = TextRecette.Value

Worksheets("S02").Range("P5").Value = TextBancontact.Value

Worksheets("S02").Range("P6").Value = TextSodexo.Value

Worksheets("S02").Range("P8").Value = TextBonQuali.Value

Worksheets("S02").Range("K11").Value = Textnomdiver1.Value

Worksheets("S02").Range("K12").Value = Textnomdiver2.Value

Worksheets("S02").Range("L11").Value = TextDiver1.Value

Worksheets("S02").Range("L12").Value = TextDiver2.Value

Worksheets("S02").Range("L22").Value = Textsolde.Value

Worksheets("S02").Range("K23").Value = Text500M.Value

Worksheets("S02").Range("K24").Value = Text200M.Value

Worksheets("S02").Range("K25").Value = Text100M.Value

Worksheets("S02").Range("K26").Value = Text50M.Value

Worksheets("S02").Range("K27").Value = Text20M.Value

Worksheets("S02").Range("K28").Value = Text10M.Value

Worksheets("S02").Range("K29").Value = Text5M.Value

Worksheets("S02").Range("J30").Value = Text2M.Value

Worksheets("S02").Range("J31").Value = Text1M.Value

Worksheets("S02").Range("J32").Value = Text05M.Value

Worksheets("S02").Range("J33").Value = Text02M.Value

Worksheets("S02").Range("J34").Value = Text01M.Value

Worksheets("S02").Range("J35").Value = Text005M.Value

Worksheets("S02").Range("J36").Value = Text002M.Value

Worksheets("S02").Range("K44").Value = Text500B.Value

Worksheets("S02").Range("K45").Value = Text200B.Value

Worksheets("S02").Range("K46").Value = Text100B.Value

Worksheets("S02").Range("K47").Value = Text50B.Value

Worksheets("S02").Range("K48").Value = Text20B.Value

Worksheets("S02").Range("K49").Value = Text10B.Value

Worksheets("S02").Range("K50").Value = Text5B.Value

Worksheets("S02").Range("I15").Value = Comboperso1B.Value

Worksheets("S02").Range("I16").Value = Comboperso2B.Value

Worksheets("S02").Range("I17").Value = Comboperso3B.Value

Worksheets("S02").Range("I18").Value = Comboperso4B.Value

Worksheets("S02").Range("I19").Value = Comboperso5B.Value

Worksheets("S02").Range("K15").Value = Textperso1B.Value

Worksheets("S02").Range("K16").Value = Textperso2B.Value

Worksheets("S02").Range("K17").Value = Textperso3B.Value

Worksheets("S02").Range("K18").Value = Textperso4B.Value

Worksheets("S02").Range("K19").Value = Textperso5B.Value

Else

If Combosemaine = "2" And Combojour = "Vendredi" Then

Worksheets("S02").Range("T4").Value = TextRecette.Value

Worksheets("S02").Range("T5").Value = TextBancontact.Value

Worksheets("S02").Range("T6").Value = TextSodexo.Value

Worksheets("S02").Range("T8").Value = TextBonQuali.Value

Worksheets("S02").Range("S11").Value = Textnomdiver1.Value

Worksheets("S02").Range("S12").Value = Textnomdiver2.Value

Worksheets("S02").Range("T11").Value = TextDiver1.Value

Worksheets("S02").Range("T12").Value = TextDiver2.Value

Worksheets("S02").Range("T22").Value = Textsolde.Value

Worksheets("S02").Range("S23").Value = Text500M.Value

Worksheets("S02").Range("S24").Value = Text200M.Value

Worksheets("S02").Range("S25").Value = Text100M.Value

Worksheets("S02").Range("S26").Value = Text50M.Value

Worksheets("S02").Range("S27").Value = Text20M.Value

Worksheets("S02").Range("S28").Value = Text10M.Value

Worksheets("S02").Range("S29").Value = Text5M.Value

Worksheets("S02").Range("R30").Value = Text2M.Value

Worksheets("S02").Range("R31").Value = Text1M.Value

Worksheets("S02").Range("R32").Value = Text05M.Value

Worksheets("S02").Range("R33").Value = Text02M.Value

Worksheets("S02").Range("R34").Value = Text01M.Value

Worksheets("S02").Range("R35").Value = Text005M.Value

Worksheets("S02").Range("R36").Value = Text002M.Value

Worksheets("S02").Range("S44").Value = Text500B.Value

Worksheets("S02").Range("S45").Value = Text200B.Value

Worksheets("S02").Range("S46").Value = Text100B.Value

Worksheets("S02").Range("S47").Value = Text50B.Value

Worksheets("S02").Range("S48").Value = Text20B.Value

Worksheets("S02").Range("S49").Value = Text10B.Value

Worksheets("S02").Range("S50").Value = Text5B.Value

Worksheets("S02").Range("Q15").Value = Comboperso1B.Value

Worksheets("S02").Range("Q16").Value = Comboperso2B.Value

Worksheets("S02").Range("Q17").Value = Comboperso3B.Value

Worksheets("S02").Range("Q18").Value = Comboperso4B.Value

Worksheets("S02").Range("Q19").Value = Comboperso5B.Value

Worksheets("S02").Range("S15").Value = Textperso1B.Value

Worksheets("S02").Range("S16").Value = Textperso2B.Value

Worksheets("S02").Range("S17").Value = Textperso3B.Value

Worksheets("S02").Range("S18").Value = Textperso4B.Value

Worksheets("S02").Range("S19").Value = Textperso5B.Value

Else

If Combosemaine = "2" And Combojour = "Samedi" Then

Worksheets("S02").Range("X4").Value = TextRecette.Value

Worksheets("S02").Range("X5").Value = TextBancontact.Value

Worksheets("S02").Range("X6").Value = TextSodexo.Value

Worksheets("S02").Range("X8").Value = TextBonQuali.Value

Worksheets("S02").Range("W11").Value = Textnomdiver1.Value

Worksheets("S02").Range("W12").Value = Textnomdiver2.Value

Worksheets("S02").Range("X11").Value = TextDiver1.Value

Worksheets("S02").Range("X12").Value = TextDiver2.Value

Worksheets("S02").Range("X22").Value = Textsolde.Value

Worksheets("S02").Range("W23").Value = Text500M.Value

Worksheets("S02").Range("W24").Value = Text200M.Value

Worksheets("S02").Range("W25").Value = Text100M.Value

Worksheets("S02").Range("W26").Value = Text50M.Value

Worksheets("S02").Range("W27").Value = Text20M.Value

Worksheets("S02").Range("W28").Value = Text10M.Value

Worksheets("S02").Range("W29").Value = Text5M.Value

Worksheets("S02").Range("V30").Value = Text2M.Value

Worksheets("S02").Range("V31").Value = Text1M.Value

Worksheets("S02").Range("V32").Value = Text05M.Value

Worksheets("S02").Range("V33").Value = Text02M.Value

Worksheets("S02").Range("V34").Value = Text01M.Value

Worksheets("S02").Range("V35").Value = Text005M.Value

Worksheets("S02").Range("V36").Value = Text002M.Value

Worksheets("S02").Range("W44").Value = Text500B.Value

Worksheets("S02").Range("W45").Value = Text200B.Value

Worksheets("S02").Range("W46").Value = Text100B.Value

Worksheets("S02").Range("W47").Value = Text50B.Value

Worksheets("S02").Range("W48").Value = Text20B.Value

Worksheets("S02").Range("W49").Value = Text10B.Value

Worksheets("S02").Range("W50").Value = Text5B.Value

Worksheets("S02").Range("U15").Value = Comboperso1B.Value

Worksheets("S02").Range("U16").Value = Comboperso2B.Value

Worksheets("S02").Range("U17").Value = Comboperso3B.Value

Worksheets("S02").Range("U18").Value = Comboperso4B.Value

Worksheets("S02").Range("U19").Value = Comboperso5B.Value

Worksheets("S02").Range("W15").Value = Textperso1B.Value

Worksheets("S02").Range("W16").Value = Textperso2B.Value

Worksheets("S02").Range("W17").Value = Textperso3B.Value

Worksheets("S02").Range("W18").Value = Textperso4B.Value

Worksheets("S02").Range("W19").Value = Textperso5B.Value

Else ' semaine 3

If Combosemaine = "3" And Combojour = "Lundi" Then

Worksheets("S03").Range("D4").Value = TextRecette.Value

Worksheets("S03").Range("D5").Value = TextBancontact.Value

Worksheets("S03").Range("D6").Value = TextSodexo.Value

Worksheets("S03").Range("D8").Value = TextBonQuali.Value

Worksheets("S03").Range("C11").Value = Textnomdiver1.Value

Worksheets("S03").Range("C12").Value = Textnomdiver2.Value

Worksheets("S03").Range("D11").Value = TextDiver1.Value

Worksheets("S03").Range("D12").Value = TextDiver2.Value

Worksheets("S03").Range("D22").Value = Textsolde.Value

Worksheets("S03").Range("C23").Value = Text500M.Value

Worksheets("S03").Range("C24").Value = Text200M.Value

Worksheets("S03").Range("C25").Value = Text100M.Value

Worksheets("S03").Range("C26").Value = Text50M.Value

Worksheets("S03").Range("C27").Value = Text20M.Value

Worksheets("S03").Range("C28").Value = Text10M.Value

Worksheets("S03").Range("C29").Value = Text5M.Value

Worksheets("S03").Range("B30").Value = Text2M.Value

Worksheets("S03").Range("B31").Value = Text1M.Value

Worksheets("S03").Range("B32").Value = Text05M.Value

Worksheets("S03").Range("B33").Value = Text02M.Value

Worksheets("S03").Range("B34").Value = Text01M.Value

Worksheets("S03").Range("B35").Value = Text005M.Value

Worksheets("S03").Range("B36").Value = Text002M.Value

Worksheets("S03").Range("C44").Value = Text500B.Value

Worksheets("S03").Range("C45").Value = Text200B.Value

Worksheets("S03").Range("C46").Value = Text100B.Value

Worksheets("S03").Range("C47").Value = Text50B.Value

Worksheets("S03").Range("C48").Value = Text20B.Value

Worksheets("S03").Range("C49").Value = Text10B.Value

Worksheets("S03").Range("C50").Value = Text5B.Value

Worksheets("S03").Range("A15").Value = Comboperso1B.Value

Worksheets("S03").Range("A16").Value = Comboperso2B.Value

Worksheets("S03").Range("A17").Value = Comboperso3B.Value

Worksheets("S03").Range("A18").Value = Comboperso4B.Value

Worksheets("S03").Range("A19").Value = Comboperso5B.Value

Worksheets("S03").Range("C15").Value = Textperso1B.Value

Worksheets("S03").Range("C16").Value = Textperso2B.Value

Worksheets("S03").Range("C17").Value = Textperso3B.Value

Worksheets("S03").Range("C18").Value = Textperso4B.Value

Worksheets("S03").Range("C19").Value = Textperso5B.Value

Else

If Combosemaine = "3" And Combojour = "Mardi" Then

Worksheets("S03").Range("H4").Value = TextRecette.Value

Worksheets("S03").Range("H5").Value = TextBancontact.Value

Worksheets("S03").Range("H6").Value = TextSodexo.Value

Worksheets("S03").Range("H8").Value = TextBonQuali.Value

Worksheets("S03").Range("G11").Value = Textnomdiver1.Value

Worksheets("S03").Range("G12").Value = Textnomdiver2.Value

Worksheets("S03").Range("H11").Value = TextDiver1.Value

Worksheets("S03").Range("H12").Value = TextDiver2.Value

Worksheets("S03").Range("H22").Value = Textsolde.Value

Worksheets("S03").Range("H23").Value = Text500M.Value

Worksheets("S03").Range("H24").Value = Text200M.Value

Worksheets("S03").Range("H25").Value = Text100M.Value

Worksheets("S03").Range("H26").Value = Text50M.Value

Worksheets("S03").Range("H27").Value = Text20M.Value

Worksheets("S03").Range("H28").Value = Text10M.Value

Worksheets("S03").Range("H29").Value = Text5M.Value

Worksheets("S03").Range("F30").Value = Text2M.Value

Worksheets("S03").Range("F31").Value = Text1M.Value

Worksheets("S03").Range("F32").Value = Text05M.Value

Worksheets("S03").Range("F33").Value = Text02M.Value

Worksheets("S03").Range("F34").Value = Text01M.Value

Worksheets("S03").Range("F35").Value = Text005M.Value

Worksheets("S03").Range("F36").Value = Text002M.Value

Worksheets("S03").Range("G44").Value = Text500B.Value

Worksheets("S03").Range("G45").Value = Text200B.Value

Worksheets("S03").Range("G46").Value = Text100B.Value

Worksheets("S03").Range("G47").Value = Text50B.Value

Worksheets("S03").Range("G48").Value = Text20B.Value

Worksheets("S03").Range("G49").Value = Text10B.Value

Worksheets("S03").Range("G50").Value = Text5B.Value

Worksheets("S03").Range("E15").Value = Comboperso1B.Value

Worksheets("S03").Range("E16").Value = Comboperso2B.Value

Worksheets("S03").Range("E17").Value = Comboperso3B.Value

Worksheets("S03").Range("E18").Value = Comboperso4B.Value

Worksheets("S03").Range("E19").Value = Comboperso5B.Value

Worksheets("S03").Range("G15").Value = Textperso1B.Value

Worksheets("S03").Range("G16").Value = Textperso2B.Value

Worksheets("S03").Range("G17").Value = Textperso3B.Value

Worksheets("S03").Range("G18").Value = Textperso4B.Value

Worksheets("S03").Range("G19").Value = Textperso5B.Value

Else

If Combosemaine = "3" And Combojour = "Mercredi" Then

Worksheets("S03").Range("L4").Value = TextRecette.Value

Worksheets("S03").Range("L5").Value = TextBancontact.Value

Worksheets("S03").Range("L6").Value = TextSodexo.Value

Worksheets("S03").Range("L8").Value = TextBonQuali.Value

Worksheets("S03").Range("K11").Value = Textnomdiver1.Value

Worksheets("S03").Range("K12").Value = Textnomdiver2.Value

Worksheets("S03").Range("L11").Value = TextDiver1.Value

Worksheets("S03").Range("L12").Value = TextDiver2.Value

Worksheets("S03").Range("L22").Value = Textsolde.Value

Worksheets("S03").Range("K23").Value = Text500M.Value

Worksheets("S03").Range("K24").Value = Text200M.Value

Worksheets("S03").Range("K25").Value = Text100M.Value

Worksheets("S03").Range("K26").Value = Text50M.Value

Worksheets("S03").Range("K27").Value = Text20M.Value

Worksheets("S03").Range("K28").Value = Text10M.Value

Worksheets("S03").Range("K29").Value = Text5M.Value

Worksheets("S03").Range("J30").Value = Text2M.Value

Worksheets("S03").Range("J31").Value = Text1M.Value

Worksheets("S03").Range("J32").Value = Text05M.Value

Worksheets("S03").Range("J33").Value = Text02M.Value

Worksheets("S03").Range("J34").Value = Text01M.Value

Worksheets("S03").Range("J35").Value = Text005M.Value

Worksheets("S03").Range("J36").Value = Text002M.Value

Worksheets("S03").Range("K44").Value = Text500B.Value

Worksheets("S03").Range("K45").Value = Text200B.Value

Worksheets("S03").Range("K46").Value = Text100B.Value

Worksheets("S03").Range("K47").Value = Text50B.Value

Worksheets("S03").Range("K48").Value = Text20B.Value

Worksheets("S03").Range("K49").Value = Text10B.Value

Worksheets("S03").Range("K50").Value = Text5B.Value

Worksheets("S03").Range("I15").Value = Comboperso1B.Value

Worksheets("S03").Range("I16").Value = Comboperso2B.Value

Worksheets("S03").Range("I17").Value = Comboperso3B.Value

Worksheets("S03").Range("I18").Value = Comboperso4B.Value

Worksheets("S03").Range("I19").Value = Comboperso5B.Value

Worksheets("S03").Range("K15").Value = Textperso1B.Value

Worksheets("S03").Range("K16").Value = Textperso2B.Value

Worksheets("S03").Range("K17").Value = Textperso3B.Value

Worksheets("S03").Range("K18").Value = Textperso4B.Value

Worksheets("S03").Range("K19").Value = Textperso5B.Value

Else

If Combosemaine = "3" And Combojour = "Jeudi" Then

Worksheets("S03").Range("P4").Value = TextRecette.Value

Worksheets("S03").Range("P5").Value = TextBancontact.Value

Worksheets("S03").Range("P6").Value = TextSodexo.Value

Worksheets("S03").Range("P8").Value = TextBonQuali.Value

Worksheets("S03").Range("K11").Value = Textnomdiver1.Value

Worksheets("S03").Range("K12").Value = Textnomdiver2.Value

Worksheets("S03").Range("L11").Value = TextDiver1.Value

Worksheets("S03").Range("L12").Value = TextDiver2.Value

Worksheets("S03").Range("L22").Value = Textsolde.Value

Worksheets("S03").Range("K23").Value = Text500M.Value

Worksheets("S03").Range("K24").Value = Text200M.Value

Worksheets("S03").Range("K25").Value = Text100M.Value

Worksheets("S03").Range("K26").Value = Text50M.Value

Worksheets("S03").Range("K27").Value = Text20M.Value

Worksheets("S03").Range("K28").Value = Text10M.Value

Worksheets("S03").Range("K29").Value = Text5M.Value

Worksheets("S03").Range("J30").Value = Text2M.Value

Worksheets("S03").Range("J31").Value = Text1M.Value

Worksheets("S03").Range("J32").Value = Text05M.Value

Worksheets("S03").Range("J33").Value = Text02M.Value

Worksheets("S03").Range("J34").Value = Text01M.Value

Worksheets("S03").Range("J35").Value = Text005M.Value

Worksheets("S03").Range("J36").Value = Text002M.Value

Worksheets("S03").Range("K44").Value = Text500B.Value

Worksheets("S03").Range("K45").Value = Text200B.Value

Worksheets("S03").Range("K46").Value = Text100B.Value

Worksheets("S03").Range("K47").Value = Text50B.Value

Worksheets("S03").Range("K48").Value = Text20B.Value

Worksheets("S03").Range("K49").Value = Text10B.Value

Worksheets("S03").Range("K50").Value = Text5B.Value

Worksheets("S03").Range("I15").Value = Comboperso1B.Value

Worksheets("S03").Range("I16").Value = Comboperso2B.Value

Worksheets("S03").Range("I17").Value = Comboperso3B.Value

Worksheets("S03").Range("I18").Value = Comboperso4B.Value

Worksheets("S03").Range("I19").Value = Comboperso5B.Value

Worksheets("S03").Range("K15").Value = Textperso1B.Value

Worksheets("S03").Range("K16").Value = Textperso2B.Value

Worksheets("S03").Range("K17").Value = Textperso3B.Value

Worksheets("S03").Range("K18").Value = Textperso4B.Value

Worksheets("S03").Range("K19").Value = Textperso5B.Value

Else

...

Le 17/01/2018 à 23:34

bonsoir,

proposition de simplification du code, non testé car pas de fichier.

Sub aargh()
    sh = "S" & Format(Val(combosemaine), "00")
    Select Case combojour
    Case "Lundi"
        origine = "A1"
    Case "Mardi"
        origine = "D1"
    Case "Mercredi"
        origine = "H1"
    Case "Jeudi"
        origine = "L1"
    Case "Vendredi"
        orgine = "P1"
    Case "Samedi"
        origine = "T1"
    Case Else
        MsgBox "erreur de jour"
        Exit Sub
    End Select
    With Worksheets(sh).Range(origine)
        .Range("D4").Value = TextRecette.Value
        .Range("D5").Value = TextBancontact.Value
        .Range("D6").Value = TextSodexo.Value
        .Range("D8").Value = TextBonQuali.Value
        .Range("C11").Value = Textnomdiver1.Value
        .Range("C12").Value = Textnomdiver2.Value
        .Range("D11").Value = TextDiver1.Value
        .Range("D12").Value = TextDiver2.Value
        .Range("D22").Value = Textsolde.Value
        .Range("C23").Value = Text500M.Value
        .Range("C24").Value = Text200M.Value
        .Range("C25").Value = Text100M.Value
        .Range("C26").Value = Text50M.Value
        .Range("C27").Value = Text20M.Value
        .Range("C28").Value = Text10M.Value
        .Range("C29").Value = Text5M.Value
        .Range("B30").Value = Text2M.Value
        .Range("B31").Value = Text1M.Value
        .Range("B32").Value = Text05M.Value
        .Range("B33").Value = Text02M.Value
        .Range("B34").Value = Text01M.Value
        .Range("B35").Value = Text005M.Value
        .Range("B36").Value = Text002M.Value
        .Range("C44").Value = Text500B.Value
        .Range("C45").Value = Text200B.Value
        .Range("C46").Value = Text100B.Value
        .Range("C47").Value = Text50B.Value
        .Range("C48").Value = Text20B.Value
        .Range("C49").Value = Text10B.Value
        .Range("C50").Value = Text5B.Value
        .Range("A15").Value = Comboperso1B.Value
        .Range("A16").Value = Comboperso2B.Value
        .Range("A17").Value = Comboperso3B.Value
        .Range("A18").Value = Comboperso4B.Value
        .Range("A19").Value = Comboperso5B.Value
        .Range("C15").Value = Textperso1B.Value
        .Range("C16").Value = Textperso2B.Value
        .Range("C17").Value = Textperso3B.Value
        .Range("C18").Value = Textperso4B.Value
        .Range("C19").Value = Textperso5B.Value
    End If
End Sub
Rechercher des sujets similaires à "raccourcir procedure"