Concaténer des cellules non adjacentes sans doublons

Bonjour,

Tout d'abord, je suis très novice en VBA.

J'ai trouvé en ligne ce code qui permet de créer une fonction personnalisée pour concaténer les valeurs d'une plage en excluant les doublons, j'en comprends la logique, mais j'aurais besoins d'utiliser une liste de cellules non adjacentes plutôt qu'une plage.

Exemple:

Une fois la formule créée, je voudrais pouvoir l'écrire dans Excel =concat(A1;B5;D12;...) et non =concat(A1:C15)

Voici le code que j'ai trouvé (et qui est super pour une plage):

Function Concat(Plage As Range) As String

Dim Dico As Object, Txt As String, C As Range

Application.Volatile

Set Dico = CreateObject("Scripting.Dictionary")

Txt = ""

For Each C In Plage

If Not Dico.exists(C.Value) Then

Dico.Add C.Value, C.Value

Txt = Txt & " / " & C.Value

End If

Next C

Concat = Right(Txt, Len(Txt) - 2)

End Function

Merci d'avance

Bonjour,

Une suggestion ... non testée ...

Sélectionne tes cellules non adjacentes, garde le curseur sur une > bouton droite > Définir un nom.

Disons "MaPlage" > tu pourras : concat("MaPlage").

ric

Ma fonction concat va être utilisée dans une fonction matricielle : les cellules que j'ai besoin de concaténer ne sont pas toujours les mêmes, elles dépendent de plusieurs critères.

Ça aurait été trop simple. LOL

En fait je me rends compte que de la façon dont ma fonction est définie, elle ne prend qu'1 seul argument (plage ou cellule unique), alors que moi j'aurais besoin qu'elle en prenne un nombre indéfini.

Je n'ai aucune idée de comment faire ça.

Vincent

Bonjour,

ci-jointe une fonction répondant à votre demande avec exemple ci-joint

Function Concat(ParamArray arguments()) As Variant
    Dim i As Integer
    Dim arg_séparateur As String, séparateur As String
    Dim valeurs As Object
    Dim plages As Range, plage As Range, cell As Range
    Application.Volatile

    '// initialisation fonction et séparateur
    Concat = CVErr(xlErrValue)
    arg_séparateur = "|"  'valeur par défaut

    '// détermination plages et séparateur selon arguments fournis
    For i = 0 To UBound(arguments)
        If IsObject(arguments(i)) Then
            If plages Is Nothing Then Set plages = arguments(i) _
            Else Set plages = Union(plages, arguments(i))
        Else
            arg_séparateur = arguments(i)
        End If
    Next i

    '// concaténation sans doublon des valeurs des plages
    Set valeurs = CreateObject("Scripting.Dictionary")
    Concat = Empty: séparateur = Empty
    For Each plage In plages.Areas
        For Each cell In plage
            If Not valeurs.exists(cell.Value) Then
                valeurs(cell.Value) = cell.Address
                If valeurs.Count > 1 Then séparateur = arg_séparateur
                Concat = Concat & séparateur & cell.Value
            End If
        Next cell
    Next plage

End Function

Merci Thev, ça marche super bien.

Vincent

J'ai un autre problème maintenant :

Je rentre cette fonction dans une formule matricielle pour que ça prenne certaines cellules qui répondent à des critères.

Les cellules qui ne répondent pas aux critères renvoient la valeur "FAUX", et ¸a a l'air de bloquer la fonction qui me renvoie #VALEUR!.

Vinamael a écrit :

Les cellules qui ne répondent pas aux critères renvoient la valeur "FAUX", et ¸a a l'air de bloquer la fonction qui me renvoie #VALEUR!.

cela me parait logique car la fonction attend dans ses arguments soit une plage, soit une chaîne de carctères. Tout autre argument provoque une erreur.

Il vous suffit de tester ce cas.

=SIERREUR(concat(arg1;arg2;...);"")

Mais si je fais ça, ça va juste me donner une cellule vide au lieu d'une erreur non ?

A priori il semble que la fonction attende comme arguments uniquement des plages, et non des chaînes de caractères, c'est probablement de là que vient le problème.

Après j'ai peur aussi que la fonction reconnaisse tout ce qui rentre par la fonction matricielle comme une chaîne et non comme des arguments séparés.

Si tu regardes ma capture d'écran, il y a bien des ";" mais c'est toute une chaîne qui est rentrée comme argument 1 alors que j'aurais besoin que chaque valeur rentre comme un argument séparé.

C'est pénible quand même d'arriver à comprendre d'où le problème vient, mais de ne pas avoir les connaissances suffisantes pour le corriger.

Vinamael a écrit :

A priori il semble que la fonction attende comme arguments uniquement des plages, et non des chaînes de caractère

La fonction nécessite au niveau des arguments au moins une plage, les chaînes de caractère sont interprétées comme séparateur des valeurs à concaténer.

Il faudrait que tu fournisses un fichier exemple et non une image dont on peut rien faire.

J'ai épuré mon fichier pour ne garder que ce qui nous intéresse.

C'est un fichier pour des émissions de TV.

Dans l'onglet "Estimés", j'ai toutes mes émissions avec le jour, l'heure de début et l'heure de fin.

L'autre onglet ce sont les émissions qui ont été diffusées l'année dernière. Il est monté de façon assez similaire.

La formule sur la quelle je m'arrache les cheveux est en L5 dans l'onglet "Estimés".

Ce que je veux faire, c'est aller chercher (et concaténer) la liste de toutes les émissions diffusées l'année dernière les mêmes jours aux mêmes heures.

Je suis sûr qu'il y a une façon de faire ça entièrement en VBA, avec des boucles pour venir ajouter des titres comme arguments dans ma fameuse fonction pour concaténer, mais je ne sais pas comment, donc j'y suis allé avec une formule matricielle. C'est très lourd, et en plus ça marche pas.

Je te laisse jeter un coup d’œil.

Merci pour ton temps, c'est vraiment apprécié.

Vincent

Bonsoir,

Si tu avais dès le départ fourni un fichier exemple, je t'aurais fourni la fonction adéquate.

ci-jointe proposition avec ma fonction personnalisée RECHERCHEVS.

NB: Pour que RECHERCHEVS fonctionne, il est impératif que toutes les cellules heures soient au même format.

Merci.

Pas contre ton code devient bien trop compliqué pour mon niveau de débutant, du coup j'ai du mal à en comprendre la logique.

Je pense que ça ne tient pas compte des jours de la semaine.

_ Si je copie ta formule par exemple sur la ligne 9, ça me sort toutes les émissions qui ont été diffusées de 4:30 à 5:00, or je voudrais uniquement celles du mardi et du jeudi.

_ En ligne 14 ça me renvoie #N/A. J'imagine que ta formule renvoie uniquement les émissions qui commencent et finissent exactement aux même heures, moi j'ai besoin de toutes les émissions qui ont été diffusées dans l'interval, donc ça comprend celle qui commencent avant et/ou qui finissent après.

En résumé, il faut que l'heure de début dans l'onglet "réels été 2018" soit < heure de fin dans l'onglet "estimés" ET heure de fin dans l'onglet "réels été 2018" soit > heure de début de l'onglet "Estimés" ET il faut au moins 1 jour de la semaine en commun.

Ça fait pas ma l de critères.

Pas simple mon truc...

Vincent

Bonsoir,

ci-jointe nouvelle version tenant compte des jours de semaine via ajout d'une colonne supplémentaire et de 3 fonctions.

Merci,

Je regarderai ça tranquillement demain.

Vincent