1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$5" Then Exit Sub
Dim Plage As Range
With Sheets("feuil2")
Set Plage = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
C = WorksheetFunction.CountIf(Plage, Cells(5, 2))
If C = 0 Then
MsgBox ("Article choisi n'existe pas dans la liste des Min & Max ")
Cells(5, "I") = ""
Cells(5, "J") = ""
Else
Dim i As Long
Tablo = Sheets("Feuil2").Range("A2", "C" & Sheets("feuil2").Range("C" & Rows.Count).End(xlUp).Row)
With Sheets("feuil1")
For i = LBound(Tablo, 1) To UBound(Tablo, 1)
If IsNumeric(Application.Match(CStr(Tablo(i, 1)), .Cells(5, 2), 0)) Then
.Cells(5, "I") = Tablo(i, 2)
.Cells(5, "J") = Tablo(i, 3)
End If
Next i
End With
End If
End Sub |
Partager