'****************************************************************************************** Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean ' -------------------------------------------------------------------------------------------------------------- ' FindAll - To find all instances of the1 given string and return the row numbers. ' If there are not any matches the function will return false ' -------------------------------------------------------------------------------------------------------------- On Error GoTo Err_Trap Dim rFnd As Range ' Range Object Dim iArr As Integer ' Counter for Array Dim rFirstAddress ' Address of the First Find ' ----------------- ' Clear the Array ' ----------------- Erase arMatches Set rFnd = oSht.Range(sRange).Find(what:=sText, LookIn:=xlValues, lookAt:=xlPart) If Not rFnd Is Nothing Then rFirstAddress = rFnd.Address Do Until rFnd Is Nothing iArr = iArr + 1 ReDim Preserve arMatches(iArr) arMatches(iArr) = rFnd.Row 'rFnd.Address pour adresse complete ' rFnd.Row Pour N° de ligne Set rFnd = oSht.Range(sRange).FindNext(rFnd) If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search Loop FindAll = True Else ' ---------------------- ' No Value is Found ' ---------------------- FindAll = False End If ' ----------------------- ' Error Handling ' ----------------------- Err_Trap: If Err <> 0 Then MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All" Err.Clear FindAll = False Exit Function End If End Function
Sub cherch() Dim arTemp() As String 'Temp Array Dim bFound As Boolean 'Flag Dim i1 As Integer 'Array Counter Dim ChercheX As String Dim ma_plage As String ma_plage ="A1:Z100" ' Plage de cellules dans laquelle chercher ChercheX = Sheets(1).Range("A1").value NbFeuilles = Thisworkbook.sheets.count For F = 2 to NbFeuilles -1 ' on parcrour toutes les feuilles du classeur sauf la 1 Nom_Feuil = Sheets(F).Name '--------------------------------------------------------------- bFound = FindAll(ChercheX, Sheets(Nom_Feuil), ma_plage, arTemp()) '--------------------------------------------------------------- If bFound = True Then ' Affichage dans la fenetre de debugage Debug.Print "Nb occurences : " & UBound(arTemp) ' // Suite du code si trouvé... ' Par exemple, incrémenter un compteur... Cmpt = Cmpt + UBound(arTemp) End if Next '// Ici tu gères si une seule répose trouvé ou plusieurs Select case Cmpt ' test combien d'occurences trouvées... Case > 1 then ' si plusieurs ' ..// code à faire Case 1 ' si seulement 1 ' ..// Code à faire Case 0 ' si aucune ' ..// Code à faire End select end Sub
Reste juste a trouver comment pointer vers la feuille ou l'argument est trouver