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
| '--- Déclaration des variables
Dim fProd As Worksheet, fMat As Worksheet, fPrix As Worksheet
Dim lProd As Long, lMat As Long, lPrix As Long
Dim dProd As Object, dMat As Object, dPrix As Object, dPrixU As Object
Dim tMat()
Private Sub UserForm_Initialize()
'--- On définit les variables
Set fProd = Feuil1: Set fMat = Feuil2: Set fPrix = Feuil3
lProd = fProd.[a65000].End(xlUp).Row: lMat = fMat.[a65000].End(xlUp).Row: lPrix = fPrix.[a65000].End(xlUp).Row:
Set dProd = CreateObject("Scripting.Dictionary"): Set dMat = CreateObject("Scripting.Dictionary")
Set dPrix = CreateObject("Scripting.Dictionary"): Set dPrixU = CreateObject("Scripting.Dictionary")
With fMat: tMat = fMat.Range(.Cells(2, 1), .Cells(lMat, 3)).Value: End With
'--- On charge les dictionary 1 et 3
For Each Cell In fProd.Range("a2:a" & lProd): dProd(Cell.Value) = Cell.Offset(, 1).Value: Next Cell
For Each Cell In fPrix.Range("a2:a" & lPrix): dPrix(Cell.Value) = Cell.Offset(, 2).Value: Next Cell
'--- On enregistre les valeurs de la combobox
Me.ComboBox1.List = dProd.Keys
End Sub
Private Sub ComboBox1_Change()
'--- On enregistre le code produit
If IsNumeric(Me.ComboBox1.Value) Then code = CLng(Me.ComboBox1.Value) Else: code = Me.ComboBox1.Value
'--- On modifie la valeur du Label
tbLabel = dProd(code)
'--- On calcule le prix unitaire
'- On enregistre les CodeMatPrem et Qu
For i = LBound(tMat) To UBound(tMat)
If tMat(i, 1) = code Then
dMat(tMat(i, 2)) = tMat(i, 3)
End If
Next i
'- On boucle
For Each d In dMat.Keys
dPrixU(d) = dMat(d) * dPrix(d)
Next d
'- On modifie la valeur du prix
tbPrix = Application.Sum(dPrixU.Items)
'--- On modifie la valeur du complément le plus onéreux
tbOnereux = Application.Max(dPrixU.Items)
'--- On vide les dictionary
dMat.RemoveAll
dPrixU.RemoveAll
End Sub |
Partager