Sub recherche_nom() Dim Nom As String Dim i As Integer, j As Integer Dim wb As Workbook Dim ws_recherche As Worksheet Dim ws_resultat As Worksheet Set wb = ActiveWorkbook 'Workbooks("tennis.xls") Set ws_recherche = wb.Worksheets("BD Adhérent") Set ws_resultat = wb.Worksheets("Cotisation") For i = 5 To 103 If LenB(ws_resultat.Cells(i, 2)) Then ws_resultat.Rows(i).Delete Else Exit For End If Next Nom = ws_resultat.Range("C2") j = 5 For i = 2 To 98 If ws_recherche.Cells(i, 2) = Nom Then ws_resultat.Cells(j, 2) = ws_recherche.Cells(i, 2) ws_resultat.Cells(j, 3) = ws_recherche.Cells(i, 3) ws_resultat.Cells(j, 4) = ws_recherche.Cells(i, 4) ws_resultat.Cells(j, 5) = ws_recherche.Cells(i, 5) ws_resultat.Cells(j, 6) = ws_recherche.Cells(i, 10) j = j + 1 End If Next i End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionRange(Cells(5, 2), Cells(101, 5)).ClearContents
Range("C2,B5:F10").Select Selection.ClearContents Range("C2").Select
j = 5 For i = 2 To 98 If Workbooks(wb).Sheets(ws_recherche).Cells(i, 2) = Nom Then Workbooks(wb).Sheets(ws_resultat).Cells(j, 2) = Workbooks(wb).Sheets(ws_recherche).Cells(i, 2) Workbooks(wb).Sheets(ws_resultat).Cells(j, 3) = Workbooks(wb).Sheets(ws_recherche).Cells(i, 3) Workbooks(wb).Sheets(ws_resultat).Cells(j, 4) = Workbooks(wb).Sheets(ws_recherche).Cells(i, 4) Workbooks(wb).Sheets(ws_resultat).Cells(j, 5) = Workbooks(wb).Sheets(ws_recherche).Cells(i, 5) Workbooks(wb).Sheets(ws_resultat).Cells(j, 6) = Workbooks(wb).Sheets(ws_recherche).Cells(i, 10) j = j + 1 End If Next i*
Range(Cells(5, 2), Cells(101, 5))
... ... Sheets("Cotisation").Range("B2:G98").ClearContents For i = 2 To 98 If Sheets("BD Adhérent").Cells(i, 2) = Nom Then For j = 2 To 7 Sheets("Cotisation").Cells(i, j).End(xlUp).Offset(1, 0) = Sheets("BD Adhérent").Cells(i, j) Next End If Next ... ...