Reduire le temps de calcul d'une formule personalisée de recherche de texte

Bonjour,

J'ai créé une fonction personnalisée (mais je ne m'y connais pas) pour rechercher des noms de produits dans des tables de nom non standardisé.
Elle fonctionne bien mais nécessite un très grand temps de calcul alors que les tables de recherche ne font que grandir.

je me demande si je pouvais réduire le calcum par la création d'une table unique non recalculée à toutes les cellules de recherche, mais je ne sais pas comment.

Voici la fonction :

Function Croisade_2(ValeurRecherchee As String, TableDeRecherche As Range, NumColonne As Integer, Separator As String)
Application.Volatile

Dim NbLignes As Double
Dim CompteurValeursTrouvées As Integer
Dim CroisadeMktg As String
Dim i
Dim y

NbLignes = TableDeRecherche.Rows.Count
CompteurValeursTrouvées = 0
CroisadeMktg = ""

For i = 1 To NbLignes
    If InStr(1, TableDeRecherche(i, 1).Value, ValeurRecherchee) Then
        CompteurValeursTrouvées = CompteurValeursTrouvées + 1
        y = TableDeRecherche(i, NumColonne).Value
        If CompteurValeursTrouvées > 1 Then
          MsgBox (CroisadeMktg)
          MsgBox (y)
          MsgBox (InStr(1, CroisadeMktg, y))
          If InStr(1, CroisadeMktg, y) >= 1 Then
            CroisadeMktg = CroisadeMktg
          Else
            CroisadeMktg = CroisadeMktg & Separator & TableDeRecherche(i, NumColonne).Value
          End If
        Else
          CroisadeMktg = CroisadeMktg & TableDeRecherche(i, NumColonne).Value
        End If
    End If

Next i

Croisade_2 = CroisadeMktg

End Function

J'avoue ne pas savoir comment l’améliorer et espère par vos lumières réussir à l'optimiser et l'accélérer.

Je vous remercie donc d'avance de votre potentielle aide et de votre indulgence sur la qualité de la dite fonction.

Bien à vous.

bonjour,

essaie d'enlever application.volatile. Dans ta fonction tous les paramètres sont identifiés, donc excel ne recalculera que si un des paramètres change, alors qu'avec application.volatile, excel recalculera dès qu'une cellule change, même si elle n'intervient pas dans la formule.

Bonjour,

Merci.

Le problème est présent même sans car je m'excuse ne pas l'avoir supprimé , c’était une de mes recherche infructueuse.

Merci encore une fois.

Bonjour,

combien de fois la fonction est-elle utilisée et combien de lignes de recherche as-tu ?

Entre une dizaine et quelques milliers.
Je m'en sers pour les regroupements n1 et multi n2 ou pour la création des listes de branches à un nœud de données.
Merci encore de l'attention prise.

bonjour,

je fais le test chez moi, une formule qui recherche parmi 10000 lignes, c'est imperceptible. mais 1000 formules qui font une recherche sur 10000 lignes tu vas le sentir. tu peux gagner en performances en utilisant une macro et une array vba et peut-être en réorganisant les données. pour ce dernier point, difficile de proposer quelque chose sans voir les données.

Je m'en doutais, c'est exactement cela le problème.
Les données sont confidentielles mais pour faire simple je cherche "noir", " noir", "noir ", "nooir", "black",... pour tout mettre dans "noir"

La macro est une solution que je vais devoir appliquer mais j'aimais la souplesse d'organisation du fichier ou des tableaux qu'offre la fonction.

Merci encore.

Bonjour,

si tu veux rester sur une fonction, .find est considérablement plus rapide que boucler sur toutes les lignes.

Exemple partiel non testé faute de fichier anonymisé :

Function Croisade_2(ValeurRecherchee As String, TableDeRecherche As Range, NumColonne As Integer, Separator As String)
    With TableDeRecherche.Resize(, 1)
        Set c = .Find(2, LookIn:=xlValues, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                MsgBox TableDeRecherche(i, NumColonne).Value & " trouvé ligne " & c.Row
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Function

Mais un simple Recherchev() ne suffit pas ?
Difficile de savoir sans fichier ni explications plus complètes sur ton besoin.
eric

mais de rien...

Bonjour,
Excusez mon délai de retour.
Je vous remercie pour votre aide.
J'essaye encore de tester votre code (quand mon emploi du temps me le permet) car celui-ci met mon excel en erreur.
Cependant, je ne désespère pas d'y arriver et évaluer sa performance.

Merci encore une fois.
Bonne journée à vous.