Private Sub CommandButton1_Click()
'OSSATURE
Dim PrixOss As Single, EssOss As String, SectOss As String, TraitOss As String, UsinOss As String, SurOss As Single, LgOss As Single, TotOss As String
EssOss = ComboBox2.Value
SectOss = ComboBox1.Value
TraitOss = ComboBox3.Value
UsinOss = ComboBox4.Value
SurOss = TextBox1.Value
LgOss = TextBox2.Value
Dim rngData As Range, rngLabelRow As Range, rngLabelColumn As Range ', fn As WorksheetFunction
'Set fn = Application.WorksheetFunction
With ThisWorkbook.Sheets("Bibliotheque")
If UsinOss = "PROFILE" Then
Set rngData = .Range("H4:I31") 'definition de la plage de recherche de prix
Set rngLabelRow = .Range("C4:C31") 'definition de la plage de recherche de section
Set rngLabelColumn = .Range("H2:I2") 'definition de la plage de recherche d'essence
'With fn
PrixOss = Application.WorksheetFunction.Index(rngData, .Match(SectOss, rngLabelRow, 0), .Match(EssOss, rngLabelColumn, 0))
'End With
ElseIf UsinOss = "BRUT" Then
'changer liste déroulante des sections
Set rngLabelRow = .Range("C4:C31")
If EssOss = "Epicéa" Then
Set rngData = .Range("D4:E31")
Set rngLabelColumn = .Range("D3:E3")
Else
Set rngData = .Range("F4:G31")
Set rngLabelColumn = .Range("F3:G3")
End If
'With fn
PrixOss = Application.WorksheetFunction.Index(rngData, .Match(SectOss, rngLabelRow, 0), .Match(TraitOss, rngLabelColumn, 0))
'End With
End If
End With
If LgOss <> 0 Then
TotOss = PrixOss * LgOss
Else
TotOss = PrixOss * SurOss * 5
End If
Label35.Caption = "prix unitaire : " & PrixOss & " Prix Total : " & TotOss
With ThisWorkbook.Sheets("Feuil1")
Dim ligne As Integer 'intégration des valeurs au tableau dans la premiere ligne vide
ligne = Sheets("Feuil1").Range("a1048576").End(xlUp).Row + 1
'ligne = Sheets("Feuil1").Range("A65536").End(xlUp).Row + 1
Range("B" & ligne).Value = SectOss
Range("C" & ligne).Value = EssOss
Range("D" & ligne).Value = UsinOss & " " & TraitOss
Range("F" & ligne).Value = TotOss / PrixOss
Range("G" & ligne).Value = PrixOss
Range("H" & ligne).Value = TotOss
End With
CommandButton1.SetFocus 'permet d'enclencher le bouton commande avec la touche entrée sans court-circuiter les "boxs"
End Sub