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
| Sub Recherche()
Dim wsB As Worksheet 'Worksheet Alize
Dim wsK As Worksheet 'Worksheet Portefeuille
Dim Valeur As String
Dim nbre As Long, cptr As Long
Dim lig As Byte
Dim T_out
Set wsB = ThisWorkbook.Worksheets("Output")
Set wsK = ThisWorkbook.Worksheets("Data")
Valeur = wsB.Range("B2").Value
With wsK
nbre = Application.CountIf(.Columns(2), Valeur)
If nbre > 0 Then
ReDim T_out(1 To nbre, 1 To 4)
lig = 1
For cptr = 1 To nbre
lig = .Columns(2).Find(Valeur, .Cells(lig, 2), xlValues).Row
T_out(cptr, 1) = .Cells(lig, 1).Value
T_out(cptr, 2) = .Cells(lig, 2).Value
T_out(cptr, 3) = .Cells(lig, 3).Value
T_out(cptr, 4) = .Cells(lig, 4).Value
Next
End If
End With
Application.ScreenUpdating = False
With wsB
.Range("G2:J1000").ClearContents
.Range("G2").Resize(cptr - 1, 4) = T_out
End With
End Sub |
Partager