XL 2019 Problème avec fonction Find

darkjedi · 2 Juin 2021

Bonjour à tous,
J'ai une nouvelle fois besoin des spécialistes Excel.
Je bute sur la fonction find en vba, je vous explique mon problème.

Dans le fichier joint extremement simplifié, j'ai 2 onglets (Onglet 1 = Report avec 1 bouton pour activer la macro "Find" / Onglet 2 = feuille masquée Listes => base de référence)
Dans 1 cellule de la colonne A de "Report", si je saisis MTW5 tout fonctionne car cette valeur est présente dans la colonne B de la feuille "Listes".
Mais dans 1 cellule de la colonne A de "Report", si je saisis MTW5 A ou MTW5A ou MTW5 (A), j'ai le message d'erreur pour me signaler que cette donnée n'existe pas dans la colonne B de la feuille "Listes".
Cependant je ne devrais pas avoir de message car j'ai utilisé LookIn:=xlValues, LookAt:=xlPart dans le Find.
Les plages sont des plages dynamiques

Voici le code de ma macro où je ne vois pas mon erreur ( typage variables....) et je vous joins le fichier exemple en PJ.

Quelle est mon erreur ?

Et merci pour votre aide.

VB:

Option Explicit

Dim ws As Worksheet
Dim dernLignVC As Integer
Dim lastRow As Integer
Dim celTemoinBatch As Range
Dim valeurTemoinBatch As String
Dim temoinBatch As Range
Dim valTemoinBatch As Double
Dim maPlage As Range

Sub chercherVerifier()
    Set ws = Sheets("Report")
    Sheets("Listes").Visible = xlSheetVisible
    Sheets("Listes").Unprotect
    dernLignVC = Sheets("Listes").Range("B65536").End(xlUp).Row
    lastRow = ws.Range("A65536").End(xlUp).Row
    Set maPlage = Sheets("Listes").Range("B2:B" & dernLignVC)
                
    For Each celTemoinBatch In ws.Range("A2:A" & lastRow)
        valeurTemoinBatch = celTemoinBatch
        Set temoinBatch = maPlage.Cells.Find(What:=valeurTemoinBatch, LookIn:=xlValues, LookAt:=xlPart)
        If temoinBatch Is Nothing Then
            MsgBox "Attention ce témoin n'est pas présent " & valeurTemoinBatch & " sur la feuille 'Listes' cachée. Vérifier vos données.", vbOKOnly + vbInformation, "Erreur témoin"
            Sheets("Listes").Protect
            Sheets("Listes").Visible = xlSheetHidden
            ws.Select
            celTemoinBatch.Select
            Set temoinBatch = Nothing
            Set ws = Nothing
            Set maPlage = Nothing
            End
        End If
                      
        valTemoinBatch = celTemoinBatch.Offset(, 1).Value
                      
        Select Case valTemoinBatch
            Case temoinBatch.Offset(, 3) To temoinBatch.Offset(, 2)
            Case Else
                celTemoinBatch.Offset(, 3) = "1"
        End Select
    Next celTemoinBatch
    
    Set maPlage = Nothing
    Set temoinBatch = Nothing
    Set ws = Nothing
    Sheets("Listes").Protect
    Sheets("Listes").Visible = xlSheetHidden
End Sub

darkjedi · 4 Juin 2021

Merci à tous.

Le problème est résolu. La solution proposé par @eriiic a fonctionné.
Et au lieu de faire une usine à gaz avec des variables tableau.... j'ai simplement utilisé sa fonction dans mon code. Et cela me convient parfaitement
Voici le code qui est fonctionnel mais qui est certainement à améliorer et le fichier avec le module 5 qui est la solution à mon problème

VB:

Option Explicit

Dim ws As Worksheet
Dim dernLignVC As Integer
Dim lastRow As Integer
Dim celTemoinBatch As Range
Dim temoinBatch As Range
Dim maPlage As Range
Dim valTemoinBatch As Double
Dim firstaddress As String
Dim ID As String

'fonction
Dim datas As Variant
Dim lig As Long



Sub chercherVerifierJF_Eriic()
    Set ws = Sheets("Report")...

vgendron · 2 Juin 2021

Hello
ton pb vient de ta compréhension du xlpart
si tu cherches
MW5 dans MW5 ==> il trouve
si tu cherches MW5 dans MW5625 555: il trouve

par contre.. si tu cherches
MW5 (A) dans MW5==> il ne trouve pas

la chaine cherchée doit apparaitre toute entière dans la chaine de recherche..
ce n'est pas une partie de la chaine cherchée qui doit apparaitre

darkjedi · 2 Juin 2021

Merci pour cette explication. Je comprends mieux mon erreur.
Quelle pourrait être la solution pour contourner cela car voici ce que j'aimerai faire :
Ma "Listes" contient MW5 (base de reference avec un intervalle)
Dans mon "Report" si je saisis MW5 A, j'aimerais que sa valeur chiffrée soit comparée à l'intervalle de la MW5 ("Listes"). Avec MW5, cela se fait automatiquement.
Je suis obligé de différencier MW5 s'il apparait plus de 2 fois dans la colonne A de la feuille "Report"

cathodique · 3 Juin 2021

Bonjour,

@vgendron

@darkjedi : Je n'ai pas compris ta problématique. Je me permets une suggestion, pour différencier tes lignes ajoute une colonne à gauche de ID pour y mettre un index pour chaque ligne. Ensuite, utilise FindNext plus-que tu auras des doublons exemple ci-dessous

un lien tuto fonction "Find"

J'espère que ça t'aidera.
Bonne journée.

eriiic · 3 Juin 2021

Bonjour,

Il faut que tu fasses l'inverse, chercher si un ID témoin est présent dans l'ID
Comme il ne semble pas y avoir de règle pour connaitre l'ID témoin depuis l'ID, il faut boucler sur tous les ID témoin, et ça ID par ID.
.find ne semble pas adapté, c'est plutôt 2 boucles imbriquées et utiliser Instr().
eric

cathodique · 3 Juin 2021

Re, si j'ai bien compris à tester

VB:

Option Explicit

Dim ws As Worksheet, dernLignVC As Integer, LastRow As Integer
Dim celTemoinBatch As Range, temoinBatch As Range, maPlage As Range, firstaddress As String
Sub chercherVerifier()
    Set ws = Sheets("Report")
    Sheets("Listes").Visible = xlSheetVisible
    Sheets("Listes").Unprotect
    dernLignVC = Sheets("Listes").Range("B65536").End(xlUp).Row
    LastRow = ws.Range("A65536").End(xlUp).Row
    Set maPlage = Sheets("Listes").Range("B2:B" & dernLignVC)

    For Each celTemoinBatch In ws.Range("B2:B" & LastRow)
        Set temoinBatch = maPlage.Cells.Find(What:=celTemoinBatch, LookIn:=xlValues, lookat:=xlPart)
        If Not temoinBatch Is Nothing Then
            Debug.Print celTemoinBatch.Offset(, 1), temoinBatch.Offset(, 2), temoinBatch.Offset(, 3)
            firstaddress = temoinBatch.Address
            Do
                If celTemoinBatch.Offset(, 1) <= temoinBatch.Offset(, 2) _
                   And celTemoinBatch.Offset(, 1) >= temoinBatch.Offset(, 3) Then celTemoinBatch.Offset(, 3) = "1"
                Set temoinBatch = maPlage.FindNext(temoinBatch)
            Loop While Not temoinBatch Is Nothing And temoinBatch.Address <> firstaddress

        Else
            MsgBox "Attention ce témoin n'est pas présent " & celTemoinBatch & " sur la feuille 'Listes' cachée. Vérifier vos données.", vbOKOnly + vbInformation, "Erreur témoin"
        End If
    Next
    Set maPlage = Nothing
    Set temoinBatch = Nothing
    Set ws = Nothing
        Sheets("Listes").Protect
        Sheets("Listes").Visible = xlSheetHidden
End Sub

darkjedi · 4 Juin 2021

Bonjour à tous,
@vgendron merci encore pour les explications sur le xlpart
@eriiic j'ai bien essayé de faire l'inverse, et j'ai toujours des erreurs car si le témoin n'est pas utilisé = erreur
@cathodique, j'ai essayé ton code et il permet de verifier toute la colonne mais le probleme est identique

Je vais donner plus d'explication sur ce que j'essaie de faire.
La feuille "Report" est une feuille créée par 1 SMRI après une série d'analyse.
La feuille "Listes" contient le nom et les renseignements de tous les témoins que je peux potentiellement utilisés.
Dans cette série d'analyse nous avons des échantillons ainsi que des témoins pour valider les résultats obtenus.
Le traitement des échantillons ne pose pas de problème. (résolu)
Les témoins ne peuvent etre qu'en double (résolu)
Si dans cette même serie, je dois repasser pour une raison X des témoins déjà analysés, je dois les renommer en ajoutant 1 caractère au moins pour les différencier lors du second passage.
Mon but est de savoir si tous les témoins passés dans cette serie sont bien compris dans leur intervalle de tolérance que ce soit lors du premier passage ou du second passage. J'ai essayé la fonction Find et la fonction Instr() mais sans grand résultat

Le "1" de la colonne "Remarque" correspond à 1 flag pour déterminer le type d'erreur ( 1 = hors limite). Le flag est déjà traité.

Je vous joins le fichier avec les 3 modules. Mais le problème est de traitement en cas de renommage est toujours présent.
module 1 = celui de départ
module 2 = celui de cathodique
module 3 = mix module 1 et 2 (balayage complet de la colonne A de la feuille "Report" + flag 1 si témoin hors limite)
Peut-etre que la fonction "Find" n'est pas adapté à ce problème. Quelle peut-etre la solution de contournement (recherche d'une partie de chaine de caractère....) ?

eriiic · 4 Juin 2021

Bonjour,

un exemple de ce que je te suggérais.
Une fonction personnalisée qui te retourne l'ID témoin selon l'ID :

VB:

Function IDtemoin(ID As String) As String
    Dim datas, lig As Long
        datas = Worksheets("Listes").[B2].Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1)
        For lig = 1 To UBound(datas)
            If Left(LCase(ID), Len(datas(lig, 1))) = LCase(datas(lig, 1)) Then IDtemoin = datas(lig, 1): Exit For
        Next
End Function

Soit tu t'en sers pour des Recherchev(), soit en faisant des fonctions sur le même principe pour ramener la valeur qui t'intéresse (cible, ...), ou une macro qui traite l'ensemble de ton tableau.
eric

darkjedi · 4 Juin 2021

@eriiic merci pour le code. Je pense que c'est la bonne piste
- Creation variable tableau dynamique des temoins de la feuille "Report" en supprimant le ou les caracteres ajoutés si plusieurs passages (en utilisant ta fonction)
- Verification dans la variable tableau si les témoins sont bien dans l'intervalle de la feuille "Listes" sinon flag "1" dans la colonne "Remarque"
- Copier coller de la colonne Remarque de la variable tableau dans la feuille "Report".

Ca fait un peu usine à gaz et je n'ai jamais vraiment utilisé les variables tableau..... Va falloir que j'apprenne vite et bien.

darkjedi · 4 Juin 2021

Merci à tous.

Le problème est résolu. La solution proposé par @eriiic a fonctionné.
Et au lieu de faire une usine à gaz avec des variables tableau.... j'ai simplement utilisé sa fonction dans mon code. Et cela me convient parfaitement
Voici le code qui est fonctionnel mais qui est certainement à améliorer et le fichier avec le module 5 qui est la solution à mon problème

VB:

Option Explicit

Dim ws As Worksheet
Dim dernLignVC As Integer
Dim lastRow As Integer
Dim celTemoinBatch As Range
Dim temoinBatch As Range
Dim maPlage As Range
Dim valTemoinBatch As Double
Dim firstaddress As String
Dim ID As String

'fonction
Dim datas As Variant
Dim lig As Long



Sub chercherVerifierJF_Eriic()
    Set ws = Sheets("Report")
    Sheets("Listes").Visible = xlSheetVisible
    Sheets("Listes").Unprotect
    dernLignVC = Sheets("Listes").Range("B65536").End(xlUp).Row
    lastRow = ws.Range("A65536").End(xlUp).Row
    Set maPlage = Sheets("Listes").Range("B2:B" & dernLignVC)

    For Each celTemoinBatch In ws.Range("A2:A" & lastRow)
        ID = celTemoinBatch
        ID = IDtemoin(ID)
        Set temoinBatch = maPlage.Cells.Find(What:=ID, LookIn:=xlValues, lookat:=xlPart)
        If temoinBatch Is Nothing Then
            MsgBox "Attention ce témoin n'est pas présent " & celTemoinBatch & " sur la feuille 'Listes' cachée. Vérifier vos données.", vbOKOnly + vbInformation, "Erreur témoin"
            Sheets("Listes").Protect
            Sheets("Listes").Visible = xlSheetHidden
            ws.Select
            celTemoinBatch.Select
            Set temoinBatch = Nothing
            Set ws = Nothing
            Set maPlage = Nothing
            End
        End If
                     
        valTemoinBatch = celTemoinBatch.Offset(, 1).Value
                     
        Select Case valTemoinBatch
            Case temoinBatch.Offset(, 3) To temoinBatch.Offset(, 2)
            Case Else
                celTemoinBatch.Offset(, 3) = "1"
        End Select
    Next celTemoinBatch
   
    Set maPlage = Nothing
    Set temoinBatch = Nothing
    Set ws = Nothing
    Sheets("Listes").Protect
    Sheets("Listes").Visible = xlSheetHidden
   
End Sub


Function IDtemoin(ID As String) As String
        datas = Worksheets("Listes").[B2].Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1)
        For lig = 1 To UBound(datas)
            If Left(LCase(ID), Len(datas(lig, 1))) = LCase(datas(lig, 1)) Then
                IDtemoin = datas(lig, 1)
                Exit For
            End If
        Next lig
End Function

eriiic · 4 Juin 2021

Si ton but est uniquement d'indiquer si tu es entre les bornes, je préparais une fonction pour ça :

VB:

Function IDin(ID As String, valeur As Double)
    Dim datas, lig As Long
    datas = Worksheets("Listes").[B2].Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1, 4)
    IDin = "Inconnu"
    For lig = 1 To UBound(datas)
        If Left(LCase(ID), Len(datas(lig, 1))) = LCase(datas(lig, 1)) Then
            IDin = IIf(valeur >= datas(lig, 2) And valeur <= datas(lig, 3), "", 1)
            Exit For
        End If
    Next
End Function

seulement je ne trouve pas pareil que toi (?) J'ai peut-être raté un critère...
pour moi MTW5 est dans les bornes par exemple
eric

Edit 19:08 : ajout exit for et "inconnu" oubliés

XL 2019 Problème avec fonction Find

darkjedi

XLDnaute Nouveau

Pièces jointes

darkjedi

vgendron

XLDnaute Barbatruc

darkjedi

XLDnaute Nouveau

cathodique

XLDnaute Barbatruc

eriiic

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

darkjedi

XLDnaute Nouveau

Pièces jointes

eriiic

XLDnaute Barbatruc

Pièces jointes

darkjedi

XLDnaute Nouveau

darkjedi

XLDnaute Nouveau

Pièces jointes

eriiic

XLDnaute Barbatruc

Discussions similaires