Raccourcir les codes

Le 04/01/2016 à 14:36

Salut à tous,

dans mon projet j'ai des codes que je n'arrive pas à raccourcir donc ils sont tellement volumineux que je risque de me perdre. Aidez moi à les raccourcir.

Merci.

Mon fichier étant trop volumineux voici un exemple de deux codes que j'utilise:

Private Sub CommandButton2_Click()

If MsgBox("Voulez-vous enregistrer cette saisie ?", vbYesNo, "Visite de classe") = vbYes Then

Range("C2").Value = TextBox1.Value

Range("D2").Value = TextBox2.Value

Range("E2").Value = TextBox3.Value

Range("F2").Value = TextBox4.Value

Range("G2").Value = TextBox5.Value

Range("H2").Value = TextBox6.Value

Range("I2").Value = TextBox7.Value

Range("J2").Value = TextBox8.Value

Range("K2").Value = TextBox9.Value

Range("L2").Value = TextBox10.Value

Range("M2").Value = TextBox11.Value

Range("N2").Value = TextBox12.Value

Range("C3").Value = TextBox14.Value

Range("D3").Value = TextBox15.Value

Range("E3").Value = TextBox16.Value

Range("F3").Value = TextBox17.Value

Range("G3").Value = TextBox18.Value

Range("H3").Value = TextBox19.Value

Range("I3").Value = TextBox20.Value

Range("J3").Value = TextBox21.Value

Range("K3").Value = TextBox22.Value

Range("L3").Value = TextBox23.Value

Range("M3").Value = TextBox24.Value

Range("N3").Value = TextBox25.Value

Range("C4").Value = TextBox27.Value

Range("D4").Value = TextBox28.Value

Range("E4").Value = TextBox29.Value

Range("F4").Value = TextBox30.Value

Range("G4").Value = TextBox31.Value

Range("H4").Value = TextBox32.Value

Range("I4").Value = TextBox33.Value

Range("J4").Value = TextBox34.Value

Range("K4").Value = TextBox35.Value

Range("L4").Value = TextBox36.Value

Range("M4").Value = TextBox37.Value

Range("N4").Value = TextBox38.Value

Range("C5").Value = TextBox40.Value

Range("D5").Value = TextBox41.Value

Range("E5").Value = TextBox42.Value

Range("F5").Value = TextBox43.Value

Range("G5").Value = TextBox44.Value

Range("H5").Value = TextBox45.Value

Range("I5").Value = TextBox46.Value

Range("J5").Value = TextBox47.Value

Range("K5").Value = TextBox48.Value

Range("L5").Value = TextBox49.Value

Range("M5").Value = TextBox50.Value

Range("N5").Value = TextBox51.Value

Range("C6").Value = TextBox53.Value

Range("D6").Value = TextBox54.Value

Range("E6").Value = TextBox55.Value

Range("F6").Value = TextBox56.Value

Range("G6").Value = TextBox57.Value

Range("H6").Value = TextBox58.Value

Range("I6").Value = TextBox59.Value

Range("J6").Value = TextBox60.Value

Range("K6").Value = TextBox61.Value

Range("L6").Value = TextBox62.Value

Range("M6").Value = TextBox63.Value

Range("N6").Value = TextBox64.Value

Range("C7").Value = TextBox66.Value

Range("D7").Value = TextBox67.Value

Range("E7").Value = TextBox68.Value

Range("F7").Value = TextBox69.Value

Range("G7").Value = TextBox70.Value

Range("H7").Value = TextBox71.Value

Range("I7").Value = TextBox72.Value

Range("J7").Value = TextBox73.Value

Range("K7").Value = TextBox74.Value

Range("L7").Value = TextBox75.Value

Range("M7").Value = TextBox76.Value

Range("N7").Value = TextBox77.Value

Range("C8").Value = TextBox79.Value

Range("D8").Value = TextBox80.Value

Range("E8").Value = TextBox81.Value

Range("F8").Value = TextBox82.Value

Range("G8").Value = TextBox83.Value

Range("H8").Value = TextBox84.Value

Range("I8").Value = TextBox85.Value

Range("J8").Value = TextBox86.Value

Range("K8").Value = TextBox87.Value

Range("L8").Value = TextBox88.Value

Range("M8").Value = TextBox89.Value

Range("N8").Value = TextBox90.Value

Range("C9").Value = TextBox92.Value

Range("D9").Value = TextBox93.Value

Range("E9").Value = TextBox94.Value

Range("F9").Value = TextBox95.Value

Range("G9").Value = TextBox96.Value

Range("H9").Value = TextBox97.Value

Range("I9").Value = TextBox98.Value

Range("J9").Value = TextBox99.Value

Range("K9").Value = TextBox100.Value

Range("L9").Value = TextBox101.Value

Range("M9").Value = TextBox102.Value

Range("N9").Value = TextBox103.Value

Range("C10").Value = TextBox105.Value

Range("D10").Value = TextBox106.Value

Range("E10").Value = TextBox107.Value

Range("F10").Value = TextBox108.Value

Range("G10").Value = TextBox109.Value

Range("H10").Value = TextBox110.Value

Range("I10").Value = TextBox111.Value

Range("J10").Value = TextBox112.Value

Range("K10").Value = TextBox113.Value

Range("L10").Value = TextBox114.Value

Range("M10").Value = TextBox115.Value

Range("N10").Value = TextBox116.Value

Range("C11").Value = TextBox118.Value

Range("D11").Value = TextBox119.Value

Range("E11").Value = TextBox120.Value

Range("F11").Value = TextBox121.Value

Range("G11").Value = TextBox122.Value

Range("H11").Value = TextBox123.Value

Range("I11").Value = TextBox124.Value

Range("J11").Value = TextBox125.Value

Range("K11").Value = TextBox126.Value

Range("L11").Value = TextBox127.Value

Range("M11").Value = TextBox128.Value

Range("N11").Value = TextBox129.Value

Range("C12").Value = TextBox131.Value

Range("D12").Value = TextBox132.Value

Range("E12").Value = TextBox133.Value

Range("F12").Value = TextBox134.Value

Range("G12").Value = TextBox135.Value

Range("H12").Value = TextBox136.Value

Range("I12").Value = TextBox137.Value

Range("J12").Value = TextBox138.Value

Range("K12").Value = TextBox139.Value

Range("L12").Value = TextBox140.Value

Range("M12").Value = TextBox141.Value

Range("N12").Value = TextBox142.Value

Range("C13").Value = TextBox144.Value

Range("D13").Value = TextBox145.Value

Range("E13").Value = TextBox146.Value

Range("F13").Value = TextBox147.Value

Range("G13").Value = TextBox148.Value

Range("H13").Value = TextBox149.Value

Range("I13").Value = TextBox150.Value

Range("J13").Value = TextBox151.Value

Range("K13").Value = TextBox152.Value

Range("L13").Value = TextBox153.Value

Range("M13").Value = TextBox154.Value

Range("N13").Value = TextBox155.Value

Range("C14").Value = TextBox157.Value

Range("D14").Value = TextBox158.Value

Range("E14").Value = TextBox159.Value

Range("F14").Value = TextBox160.Value

Range("G14").Value = TextBox161.Value

Range("H14").Value = TextBox162.Value

Range("I14").Value = TextBox163.Value

Range("J14").Value = TextBox164.Value

Range("K14").Value = TextBox165.Value

Range("L14").Value = TextBox166.Value

Range("M14").Value = TextBox167.Value

Range("N14").Value = TextBox168.Value

Range("C15").Value = TextBox170.Value

Range("D15").Value = TextBox171.Value

Range("E15").Value = TextBox172.Value

Range("F15").Value = TextBox173.Value

Range("G15").Value = TextBox174.Value

Range("H15").Value = TextBox175.Value

Range("I15").Value = TextBox176.Value

Range("J15").Value = TextBox177.Value

Range("K15").Value = TextBox178.Value

Range("L15").Value = TextBox179.Value

Range("M15").Value = TextBox180.Value

Range("N15").Value = TextBox181.Value

Range("C16").Value = TextBox183.Value

Range("D16").Value = TextBox184.Value

Range("E16").Value = TextBox185.Value

Range("F16").Value = TextBox186.Value

Range("G16").Value = TextBox187.Value

Range("H16").Value = TextBox188.Value

Range("I16").Value = TextBox189.Value

Range("J16").Value = TextBox190.Value

Range("K16").Value = TextBox191.Value

Range("L16").Value = TextBox192.Value

Range("M16").Value = TextBox193.Value

Range("N16").Value = TextBox194.Value

Range("C17").Value = TextBox196.Value

Range("D17").Value = TextBox197.Value

Range("E17").Value = TextBox198.Value

Range("F17").Value = TextBox199.Value

Range("G17").Value = TextBox200.Value

Range("H17").Value = TextBox201.Value

Range("I17").Value = TextBox202.Value

Range("J17").Value = TextBox203.Value

Range("K17").Value = TextBox204.Value

Range("L17").Value = TextBox205.Value

Range("M17").Value = TextBox206.Value

Range("N17").Value = TextBox207.Value

Range("C18").Value = TextBox209.Value

Range("D18").Value = TextBox210.Value

Range("E18").Value = TextBox211.Value

Range("F18").Value = TextBox212.Value

Range("G18").Value = TextBox213.Value

Range("H18").Value = TextBox214.Value

Range("I18").Value = TextBox215.Value

Range("J18").Value = TextBox216.Value

Range("K18").Value = TextBox217.Value

Range("L18").Value = TextBox218.Value

Range("M18").Value = TextBox219.Value

Range("N18").Value = TextBox220.Value

ThisWorkbook.Save

Unload Me

VC.Show

End If

End Sub

et le code:

Private Sub CommandButton32_Click()

Dim L As Integer

'Mamie Faitai

Sheets("PEFF").Select

If ComboBox1.Value = "" Then

MsgBox ("Veuillez remplir le champs de la recherche")

Else

TB1 = Feuil4.[B26]

TB2 = Feuil4.[C26]

TB3 = Feuil4.[D26]

TB4 = Feuil4.[E26]

TB5 = Feuil4.[B27]

TB6 = Feuil4.[C27]

TB7 = Feuil4.[D27]

TB8 = Feuil4.[E27]

TB9 = Feuil4.[F26]

TB10 = Feuil4.[G26]

TB11 = Feuil4.[H26]

TB12 = Feuil4.[I26]

TB13 = Feuil4.[J26]

TB14 = Feuil4.[K26]

TB15 = Feuil4.[L26]

TB16 = Feuil4.[M26]

TB17 = Feuil4.[N26]

TB18 = Feuil4.[O26]

TB19 = Feuil4.[F27]

TB20 = Feuil4.[G27]

TB21 = Feuil4.[H27]

TB22 = Feuil4.[I27]

TB23 = Feuil4.[J27]

TB24 = Feuil4.[K27]

TB25 = Feuil4.[L27]

TB26 = Feuil4.[M27]

TB27 = Feuil4.[N27]

TB28 = Feuil4.[O27]

TB29 = Feuil4.[P26]

TB30 = Feuil4.[Q26]

TB31 = Feuil4.[R26]

TB32 = Feuil4.[P27]

TB33 = Feuil4.[Q27]

TB34 = Feuil4.[R27]

End If

End Sub

Le 04/01/2016 à 15:08

Bonjour,

tu n'as pas pensé à une (des) boucles for / next ?

P.

Le 04/01/2016 à 15:41

Bonjour

Un essai (aucun test de fait)

Private Sub CommandButton2_Click()
Dim Ligne As Long, Col As Integer, Indice As Integer

  If MsgBox("Voulez-vous enregistrer cette saisie ?", vbYesNo, "Visite de classe") = vbYes Then
    For Ligne = 2 To 18
      For Col = 1 To 12
        Cells(Ligne, Col + 2) = Me.Controls("TextBox" & Indice + Col).Value
      Next Col
      Indice = Indice + 13
    Next Ligne
    ThisWorkbook.Save
    Unload Me
    VC.Show
  End If
End Sub

Private Sub CommandButton32_Click()
Dim I As Integer, ColLig26 As Integer, ColLig27 As Integer

  'Mamie Faitai
  Sheets("PEFF").Select
  If ComboBox1.Value = "" Then
    MsgBox ("Veuillez remplir le champs de la recherche")
  Else
    ColLig26 = 2: ColLig27 = 2
    For I = 1 To 34
      Select Case I
        Case 1 To 4, 9 To 18, 29 To 31
          Me.Controls("TB" & I) = Feuil4.Cells(26, ColLig26)
          ColLig26 = ColLig26 + 1
        Case Else
          Me.Controls("TB" & I) = Feuil4.Cells(27, ColLig27)
          ColLig27 = ColLig27 + 1
      End Select
    Next I
  End If
End Sub
Le 04/01/2016 à 16:39

Salut à tous,

et merci énormément à tous.

Merci Banzai64 car ton code est excellent. Il fonctionne parfaitement. Merci

Rechercher des sujets similaires à "raccourcir codes"