Raccourcir les codes
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
Bonjour,
tu n'as pas pensé à une (des) boucles for / next ?
P.
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
Salut à tous,
et merci énormément à tous.
Merci Banzai64 car ton code est excellent. Il fonctionne parfaitement. Merci