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 Conc()
'------------------------------------------------------
' Tableau contenant la liste des lignes trouvées
' par la méthode findAll()
'------------------------------------------------------
Dim arTemp() As String
'------------------------------------------------------
' Autres variables :
'------------------------------------------------------
Dim nomF As String
Dim n As Integer
nomF = "CODE_DTR"
Dim PlageRecherche As String
PlageRecherche = "C2:C30000"
Dim MaRange As Range
Set MaRange = Range("A2:A30000")
Dim concat As String
'------------------------------------------------------
' Début du traitement :
'------------------------------------------------------
'On parcourt la colonne A
For Each cell In MaRange
ValCherchee = cell.Value
ligneCell = cell.Row
concat = ""
If ValCherchee <> "" Then
'---------------------------------------------------------------
bFound = FindAll(ValCherchee, Sheets(nomF), PlageRecherche, arTemp())
'---------------------------------------------------------------
Debug.Print "Ligne: " & ligneCell & " : " & ValCherchee & " ---> " & bFound
If bFound = True Then
Debug.Print "Nb occurences : " & UBound(arTemp)
For X = 1 To UBound(arTemp)
Debug.Print "-------> trouvé Ligne : " & arTemp(X)
' * Ici mettre le code pour la concaténation
concat = concat & "," & Chr(10) & Cells(arTemp(X) + 1, "C").Value
Next
'* Ici : Code pour copier les valeurs trouvées dans la colonne E
If Cells(ligneCell, "C").Value <> "" Then
Cells(ligneCell, "B").Value = Right(concat, Len(concat) - 1)
Cells(ligneCell, "B").Select
End If
End If
End If
Next
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
For X = 1 To UBound(arTemp)
While Cells(arTemp(X), "C").Font.Bold = False
Debug.Print "-------> trouvé Ligne : " & arTemp(X)
' * Ici mettre le code pour la concaténation
If Cells(arTemp(X), "C").Value = ValCherchee Then
concat = concat & "," & Chr(10) & Cells(arTemp(X), "C").Value
End If
Wend
Next
'* Ici : Code pour copier les valeurs trouvées dans la colonne E
If Cells(ligneCell, "C").Value <> "" Then
Cells(ligneCell, "B").Value = Right(concat, Len(concat) - 1)
Cells(ligneCell, "B").Select
End If