Remplacement ou Adaptation de la méthode FIND

Bonjour à tous,

Je dispose d'un tableau permettant de recenser les produits vendus à un client (1 classeur par client). C'est une sorte de bon de commande.

A chaque produit vendu correspond une ligne (soit un enregistrement).

Chaque ligne est composée d'environ 60 colonnes permettant de répertorier un maximum d'information sur le produit, sa garantie, la vente,…

Ce tableau permet ensuite d'éditer un devis.

Une des colonnes (disons par exemple la colonne D) contient une formule de concaténation correspondant à environ une vingtaine de colonnes. Cette colonne de concaténation a pour but de déterminer les lignes en double.

Ainsi, pour chaque ligne de produit considéré, je cherche à comparer le contenu de la cellule en cours (résultant de la concaténation) avec les autres cellules de la même colonne situées en dessous (j'impose un ordre de tri au préalable) afin de déterminer si certains contenus sont identiques et surtout afin d'obtenir le numéro de la ligne contenant le dernier contenu identique.

Pour réaliser cela, j'ai utilisé la méthode FIND appliquée à la plage de cellules souhaitées. Tout fonctionne correctement tant que les cellules de la colonne D ne contiennent pas plus de 255 caractères. Lorsqu'une cellule comporte plus de 255 caractères, un message d'erreur apparaît.

Auriez-vous une solution permettant de traiter n'importe quel nombre de caractères avec la fonction FIND ou toute autre suggestion sera la bienvenue ?

Afin de mieux comprendre ma demande, vous trouverez ci-joint un extrait de mon fichier.

D'avance merci pour l'intérêt que vous porterez à ma question.

Bonsoir,

ton code légèrement adapté, et j'ai appris quelque chose (la limite à 255 caractères)

correction intégrée dans le fichier joint.

Option Explicit

Sub test()

Dim LignesIdentiques As Range
Dim i As Long, temp As Long, noLigneId As Long

  temp = ws_BT.UsedRange.Rows.Count

  For i = 20 To temp - 1

    Set LignesIdentiques = ws_BT.Range("D" & i + 1 & ":D" & temp).Find(Left(ws_BT.Cells(i, 4), 255), LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
    If Not LignesIdentiques Is Nothing Then
     If LignesIdentiques = ws_BT.Cells(i, 4) Then
      noLigneId = LignesIdentiques.Row
      MsgBox "La Ligne n° " & noLigneId & " est la prochaine ligne identique à la ligne n° " & i
      Set LignesIdentiques = Nothing
    End If
    End If

  Next i

End Sub

Bonjour à tous,

Merci h2so4 pour ta proposition mais malheureusement elle ne fonctionne pas à tous les coups (et c'est bien dommage) !

Le code repère uniquement la dernière cellule contenant les 255 premiers caractères identiques à la cellule de comparaison. Et si cette cette cellule ne contient pas exactement les mêmes informations (différence après les 255 premiers caractères), aucune autre cellule (située entre les deux cellules comparées) n'est repérée.

J'ai joint un exemple (la ligne 22 devrait indiquée que la ligne 26 est identique mais ce n'est pas repéré car la comparaison s'est arrêtée sur la ligne 28).

Je pense qu'il faut introduire une boucle mais je ne sais pas trop comment m'y prendre.

Encore merci à tous ceux qui pourront apporter leur pierre à l'édifice !

Bonjour,

une proposition via une méthode plus basique.

intégré en PJ

Option Explicit

Sub test()

Dim LignesIdentiques As Range
Dim i As Long, j As Long, temp As Long, noLigneId As Long

  temp = ws_BT.UsedRange.Rows.Count

  For i = 20 To temp - 2
   For j = i + 1 To temp - 1
    If ws_BT.Cells(i, 4) = ws_BT.Cells(j, 4) Then
      MsgBox "La Ligne n° " & j & " est la prochaine ligne identique à la ligne n° " & i
    End If
    Next j
  Next i

End Sub

Bonjour h2so4,

Merci pour ta dernière proposition mais celle-ci ne convenait pas à ce que souhaite.

En effet, je souhaite trouver la dernière cellule contenant la même valeur que celle comparée et je préfère éviter de parcourir toutes les lignes de ma feuille car il peut y en avoir une centaine (donc risque de lenteur à l'exécution). De plus, ce n'est qu'une partie de la procédure et elle s'exécute dans une autre boucle.

Ceci dit, j'ai trouvé une solution basée sur ta première proposition qui me convient et dont je colle le code ci-dessous.

Option Explicit

Sub test()

Dim LignesIdentiques As Range
Dim i As Long, j As Long, temp As Long, noLigneId As Long

  temp = ws_BT.UsedRange.Rows.Count

  For i = 20 To temp - 1

    Set LignesIdentiques = ws_BT.Range("D" & i + 1 & ":D" & temp).Find(Left(ws_BT.Cells(i, 4), 255), LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)

    If Not LignesIdentiques Is Nothing Then

      noLigneId = LignesIdentiques.Row

      If LignesIdentiques <> ws_BT.Cells(i, 4) Then

        For j = i + 1 To noLigneId - 1

          Set LignesIdentiques = ws_BT.Range("D" & j & ":D" & noLigneId - 1).Find(Left(ws_BT.Cells(i, 4), 255), LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)

          If Not LignesIdentiques Is Nothing Then

            noLigneId = LignesIdentiques.Row

            If LignesIdentiques = ws_BT.Cells(i, 4) Then
              MsgBox "La Ligne n° " & noLigneId & " est la prochaine ligne identique à la ligne n° " & i
              Exit For
            End If

          End If

        Next j

        Else

        MsgBox "La Ligne n° " & noLigneId & " est la prochaine ligne identique à la ligne n° " & i

      End If

    End If

    Set LignesIdentiques = Nothing

  Next i

End Sub

S'il y a plus simple et surtout plus rapide, je suis bien évidemment preneur.

Encore merci de ton aide.

Bonjour,

surtout afin d'obtenir le numéro de la ligne contenant le dernier contenu identique.

Et ta macro ressort non pas le dernier mais le suivant.

Tu veux quoi au juste ?

Sinon une suggestion : utiliser un hashcode plutôt que la concaténation.

Si tu prends un CRC base64 tu limites les risque de collisions.

Un exemple ici, tu peux en chercher d'autres sur google : http://stackoverflow.com/questions/7358955/generate-short-hash-string-based-using-vba

Et ci-joint un fichier comparatif avec différents CRC.

eric

Edit: fichier trop gros pour le site : https://www.cjoint.com/c/EBgnlgfW76c

Bonjour eriiic,

Merci pour ton intérêt à mon (ex) problème.

Tout d'abord, le bout de code que j'ai indiqué précédemment convient exactement à ce que je recherche.

Et je confirme bien obtenir le dernier contenu (la ligne la plus en bas du tableau) identique à la cellule de comparaison.

D'autre part, j'avoue ne rien comprendre à ta suggestion. Les termes "hashcode" et CRC base 64 me sont totalement inconnus.

Peux-tu me donner un peu plus de précisions à ce sujet ? Merci d'avance.

Ah oui, c'est moi qui ai lu trop vite la msgbox.

Un hashcode est une fonction qui te retourne un nombre (ou une petite chaine) basé sur la totalité de ta chaine qui la représente (presque) de façon unique.

Presque parce qu'il peut arriver que 2 chaines aient le même hashcode (c'est une collision).

Au lieu d'avoir des chaines de 500 caractères à comparer elles ne font plus 5 caractères (par exemple), ce qui permet d'utiliser le .find.

Plus la base du hashcode est élevée, plus le risque de collision est faible, voire presque nul. Au détriment du temps de calcul bien sûr.

Un exemple sur ton fichier.

Un CRC12 et un CRC64 (plus sûr mais 3 fois plus lent).

A toi de faire des tests pour estimer les éventuels risques de collisions sur ton fichier complet et pour voir si c'est plus intéressant en temps de calcul.

D'un coté concaténation + tes boucles, de l'autre concaténation + hashcode + .find

eric

Bonsoir ériiic,

Merci pour ces explications, mais je ne vois pas comment tu as appliqué ta proposition dans mon fichier Excel.

Peux-tu ajouter une petite démo directement sur mes données ?

Ta solution m'intéresse fortement surtout si cela peut faire gagner du temps dans l'exécution de ma procédure.

Bonjour,

Oupsss, il semble que j'ai oublié de sauvegarder avant d'envoyer le fichier, désolé.

J'ai refait.

Je t'ai ajouté 2 hash en H et I par fonctions personnalisées, tu choisis.

Tu vois que les hashcodes sont identiques sur les lignes 22, 26 et 28

Ensuite tu peux refaire ton .find plus simple du départ sur une de ces 2 colonnes.

Mais je te rappelle qu'il y a un choix à faire. Le risque collision (2 chaines différentes ont le même hashcode) aussi minime soit-il n'est jamais nul. Même si c'est une fois tous les 1000 ans c'est comme le tremblement de terre de san-francisco, ça peut être demain.

eric

Bonsoir eriiic,

Me voici de nouveau après quelques jours en déplacement et complètement occupé sur un autre projet.

J'ai regardé le fichier joint et maintenant je comprends mieux les notions de hashcode et CRC.

Cette solution est effectivement très intéressante et je pense que je vais réellement la mettre en œuvre même si j'ai bien noté qu'un risque de collision existe.

Encore un grand merci pour le temps que tu as bien voulu m'accorder.