Re : optimisation de macro
Re,
daterecep est une dim Date que j'utilise pour transformer la date du textbox au format date.
Private Sub CommandButton1_Click()
Dim Eprouvette As String, age As Integer
Dim datrecep As Date, datcoul As Date, yy As Range
Dim ii As Integer, c As Range, adr As String, i&, j&
Dim Aff&, ColK
'Si ComboBox1, ComboBox2 ET ComboBox3 vides alors on sort
If ComboBox1 = "" And ComboBox2 = "" And ComboBox3 = "" Then MsgBox "Aucune éprouvette n'est saisie": Exit Sub
'Si l'un des TextBox est vide ET le Tag = C alors on sort
'La propriété Tag est mise dans le module de classe
For i = 12 To 35
If Controls("TextBox" & i).Tag = "C" And Controls("TextBox" & i) = "" Then _
MsgBox "Certaines valeurs ne sont pas renseign_es": Exit Sub
Next
If "15 x 30" = ComboBox8 Then Eprouvette = 1
If "15,8 x 31,8" = ComboBox8 Then Eprouvette = 2
With Sheets("Carnet")
.Unprotect
ii = Cells(Rows.Count, 1).End(xlUp)(2).Row
For i = 1 To 3
If Controls("Combobox" & i) = "" Then GoTo Suite
For j = 1 To Controls("Combobox" & i)
Select Case j
Case Is = 1
Aff = Controls("TextBox" & (20 + (i * 4)))
ColK = Controls("TextBox" & (8 + (i * 4)))
Case Is = 2
Aff = Controls("TextBox" & (21 + (i * 4)))
ColK = Controls("TextBox" & (9 + (i * 4)))
Case Is = 3
Aff = Controls("TextBox" & (22 + (i * 4)))
ColK = Controls("TextBox" & (10 + (i * 4)))
Case Is = 4
Aff = Controls("TextBox" & (23 + (i * 4)))
ColK = Controls("TextBox" & (11 + (i * 4)))
End Select
.Range("B" & ii) = ComboBox7 'Client
.Range("I" & ii) = TextBox3 'RealisePar
.Range("J" & ii) = ComboBox10 'LieuFabriq
.Range("AC" & ii) = ComboBox11 'Fabriquant
.Range("H" & ii) = ComboBox9 'Serrage
.Range("G" & ii) = Eprouvette 'Eprouvette
.Range("S" & ii) = ComboBox12 'Dosage
.Range("T" & ii) = TextBox9 'Lieu
.Range("U" & ii) = TextBox10 'Projet
.Range("V" & ii) = TextBox11 'Partie
datrecep = TextBox1
.Range("C" & ii) = datrecep 'DateReception
datcoul = TextBox2
.Range("F" & ii) = datcoul 'DatePrelevement
.Range("W" & ii) = Aff 'Affaissement
.Range("K" & ii) = ColK 'N¡Eprouvette
age = Controls("ComboBox" & i + 3) 'AgeEprouvette
.Range("L" & ii) = age
.Range("N" & ii) = Range("L" & ii).Value + Range("F" & ii).Value
.Range("D" & ii) = Format(Range("C" & ii), "myyyy")
.Range("Q" & ii) = Format(Range("N" & ii), "myyyy")
Range("C" & ii).Select
Set yy = Cells.Find(What:=datrecep, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlUp, MatchCase:=True)
age = yy.Row
If yy = datrecep Then
Range("A" & ii) = Format(.Range("C" & ii), "yymmdd" & "01")
Else
Range("A" & ii) = Range("A" & age) + "1"
End If
ii = ii + 1
Next
Suite:
Next
.Protect
End With
Unload Me
End Sub