1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
|
Private Sub CommandButton1_Click()
Dim l_info As Integer
Dim note_1 As String, note_2 As String, lanote As String
With ThisWorkbook.Worksheets("TABLEAU RECAP")
l_info = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
.Range("B" & l_info).Value = ComEQUI 'libelle equipement'
.Range("c" & l_info).Value = Textlocal 'code local"
.Range("D" & l_info).Value = ComRESP 'Nom du responsable'
.Range("E" & l_info).Value = CDate(TextDATEAM) 'date du constat'
.Range("F" & l_info).Value = CDate(TextMISE) 'date de mise en service'
.Range("G" & l_info).Value = CInt(TextDUREVIE.Value) 'Duree de vie theorique'
.Range("H" & l_info).Value = CDate(TextREMPL) 'Date theorique de remplacement '
.Range("I" & l_info).Value = CInt(TextDURVIERESI.Value) 'Duree de vie residuelle '
.Range("J" & l_info).Value = TextESTIMREMPL 'Duree de vie residuelle '
.Range("K" & l_info).Value = CInt(TextRESUETAT.Value) 'note de etat equipement'
.Range("l" & l_info).Value = CInt(TextRESUCRIT.Value) 'note de criticite equipement'
With .Range("M" & l_info)
'formulation
.FormulaR1C1 = "=IF(RC[-2]<=21,""Mauvais"",IF(RC[-2]<=43,""Usuel"",IF(RC[-2]<=64,""Bon"")))"
'équivaut à un collage spécial valeur
.Value = .Value
note_1 = .Value
End With
With .Range("N" & l_info)
'formulation
.FormulaR1C1 = "=IF(RC[-2]<=21,""Faible"",IF(RC[-2]<=43,""Moyenne"",IF(RC[-2]<=64,""Forte"")))"
'équivaut à un collage spécial valeur
.Value = .Value
note_2 = .Value
End With
Select Case True
Case note_1 = "Mauvais" And note_2 = "Faible"
lanote = "B"
Case note_1 = "Mauvais" And note_2 = "Moyenne"
lanote = "C"
Case note_1 = "Mauvais" And note_2 = "Forte"
lanote = "C"
Case note_1 = "Usuel" And note_2 = "Faible"
lanote = "A"
Case note_1 = "Usuel" And note_2 = "Moyenne"
lanote = "B"
Case note_1 = "Usuel" And note_2 = "Forte"
lanote = "B"
Case note_1 = "Bon" And note_2 = "Faible"
lanote = "A"
Case note_1 = "Bon" And note_2 = "Moyenne"
lanote = "A"
Case note_1 = "Bon" And note_2 = "Forte"
lanote = "A"
End Select
.Range("O" & l_info).Value = lanote
Set ws = Sheets("Donné équipement")</souligne>
l = ws.Cells.Find(ComEQUI.Value, , , xlWhole).Row
ws.Range("G" & l).Value = lanote
End With
Me.hide
Unload UserFormpri
End Sub |
Partager