Affichage erreur #Valeur alors que code VBA fonctionne [Résolu/Fermé]

Signaler
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016
-
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
-
Salut à tous,

J'ai un souci d'affichage dans Excel 2013.

J'ai créé une fonction qui s'appelle Lister, en VBA. Elle me permet d'afficher une liste des valeurs que l'on trouve dans une colonne en fonction de certains paramètres. Cette fonction, lorsque je la teste debug.print lister(....), me renvoie bien ma liste: Valeur1, Valeur2,..., ValeurN ou bien N/A quand il n'y a pas d'élément de liste.

Jusqu'à la, tout va bien... Mais quand j'appel ma fonction directement dans une feuille, avec les bons paramètres, j'obtiens 2 résultats: N/A = Normal, pas de liste ou #Valeur = erreur excel!

Ma fonction retourne un string et est déclarée en public...
La seule explication que je vois c'est la mise en forme de ma cellule.

Est-ce qu'une bonne ame pourrait me dépanner?
D'avance merci

12 réponses

Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour,
Pouvez-vous donner plus de précision sur la procédure.... ?

Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Bonjour Le Pingou,

Je copie le code, il n'y en a pas beaucoup.

Public Function Lister(ByVal Id As String, ByVal Recherche As String, Optional ByVal Trouver As String) As String ' EFFECTUE LA RECHERCHE D'UNE CHAINES DE CARACTERES
' Parametres: Id= Chaine de caractere a rechercher
' Recherche= Colonne où chercher Id
' Trouver= Colonne ou prendre le résultat à lister
' Résultats: = N/A si la chaine est abscente
' = Liste de spec ayant la chaine
'#### DECLARATION DES VARIABLES ####
Dim resultat As String ' Témoin de la liste
Dim res As Range ' Témoin de la plage de recherche
Dim firstAddress As String ' Témoin de l'adresse du premier resultat
Dim ligne As Integer ' Témoin du numéro de la ligne du résultat
Dim colone As Integer ' Témoin de la colonne où trouver
Dim valeur As String ' Témoin de la valeur trouvée

'#### INITIALISATION DES VARIABLES ####
If Recherche = "" Then ' Quand il n'y a pas de parametre
resultat = "Erreur de recherche" ' Fixe la liste à la réponse d'erreur
GoTo Exit_Lister ' Sort de la fonction
Else
resultat = "N/A" ' Fixe par defaut la liste
Recherche = UCase(Recherche) ' Passe le parametre de recherche en majuscule
End If
If Trouver = "" Or Len(Trouver) <> 1 Then ' Converti la colone en nombre
colone = 1 ' Fixe par défaut la premiere colonne pour la liste
Else ' Sinon effectue la convertion
colone = Asc(UCase(Trouver)) - 64 ' Marche pour les 26 premieres colones
End If

'#### DEROULEMENT DE LA RECHERCHE####
With Worksheets(2).Range(Recherche & "1:" & Recherche & "1500")
Set res = .Find(Id, LookIn:=xlValues) ' Lance la recherche de la valeur
If Not res Is Nothing Then ' Quand on trouve au moins une fois la valeur
firstAddress = res.Address ' on enregistre l'addresse
Do
ligne = CInt(Right(res.Address, Len(res.Address) - 3)) ' Recupere le numéro de la ligne
valeur = Worksheets(2).Cells(ligne, colone).Value2 ' Et le contenu
If valeur <> Empty Then ' Quand il y a une info
If ((resultat = "N/A") Or (resultat = "")) Then ' Et si c'est la premiere valeur
resultat = valeur ' Affecte la valeur à la liste
Else ' Si ce n'est pas la premiere valeur
resultat = resultat & ", " & valeur ' Concatene la valeur
End If
Else ' Quand il n'y a pas d'info
If ((resultat = "N/A") Or (resultat = "")) Then ' Et si c'est la premiere valeur
resultat = "Ligne: " & ligne ' Affecte le N° de ligne comme valeur à la liste
Else ' Si ce n'est pas la premiere valeur
resultat = resultat & ", " & "Ligne: " & ligne ' Concatene la valeur
End If
End If
Set res = .FindNext(res) ' Recherche la valeur dans la suite
Loop While Not res Is Nothing And res.Address <> firstAddress ' Jusqu'à la fin de la plage
End If
End With
'#### FIN DU TRAITEMENT ####
Exit_Lister: ' Arret normal de la Fonction
Lister = resultat ' Retourne la liste correspndant à Id

End Function

Dans la feuille, qui appel cette fonction, les cellules sont en 'standard' (pas nombre ou texte).
Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
Bonjour,

Le format des cellules n'a rien à voir.
Fait en pas à pas, ou dépose-nous un fichier en situation avec le #VALEUR

eric
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Bonjour Eric,

J'ai préparé un fichier de test. Comment dois-je faire pour le poster?
Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
Bonjour,

Merci de déposer le fichier xls (réduit au nécessaire et anonymisé, avec les explications et éventuellement le résultat attendu) sur cjoint.com et coller ici le lien fourni.

eric
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Merci Eric de ta réponse aussi rapide... et de ton aide.

Le lien: https://www.cjoint.com/?0KuiKyGHxQy

Maol
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Sur mon fichier de travail, je viens de réaliser que lorsque ma recherche me donne aucune liste, la cellule affiche'N/A' qui est la réponse pour une liste vide. Quand la fonction trouve un ou des éléments pour la liste, il y a l'erreur #Valeur.

Est-ce que ma façon de retourner le résultat est correcte?
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour,
Au passage, salut eriiic et bonne journée.
maol: Est-ce que j'ai bien compris: ce sont les valeurs que renvoient les formules qui se trouvent dans la plage [E3:E17] de la feuille [Synthesis] et qui se réfère aux données de la feuille [DATA] qui vous pose problème... Oui / Non !
Merci de votre réponse.
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Bonjour Le Pingou,

Je viens de me rendre compte en lisant la question qu'en simplifiant j'ai effacer une page qui fausse le code:
valeur = Worksheets(2).Cells(ligne, colone).Value2
doit devenir
valeur = Worksheets(1).Cells(ligne, colone).Value2

Les infos sont dans la page DRS.
Ce qui est agaçant, c'est qu'en mode debug la fonction renvoie bien une liste...
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour,
Merci de l'information.
Donc la référence est la feuille [DRS HR900 ACCESS] et non pas [DATA].
Dans ce cas la ligne suivante est aussi fausse (se réfère à la feuille [DATA] ... !
With Worksheets(2).Range(Recherche & "1:" & Recherche & "1500")


Salutations.
Le Pingou
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Yes :-(
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour,
Désolé, pas compris...!
Salutations.
Le Pingou
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Cette ligne de code doit egalement faire reference à la page des DRS
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour,
Merci, je vais voir votre procédure en détail .... patience.

Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
Re,

déjà l'explication de ton #VALEUR. Dans :
Loop While Not c Is Nothing And res.Address <> firstAddress
res.Address ne peut pas être évaluer si res is nothing, et il est toujours calculé même si le test précédent (Not c Is Nothing) est TRUE.
Ca te génère une erreur qui te fait sortir brutalement de la fonction (oui, l'exemple de l'aide est moyen...)

Autre problème : ton .findnext ne trouve jamais de 2nde réponse. Sans doute dû à un bug de .findnext dans une fonction personnalisée.
Je te l'ai donc refaite à minima, je te laisse mettre les commentaires :
Public Function Lister2(ByVal Id As String, ByVal Recherche As String, Optional ByVal Trouver As String = "A") As Variant  ' EFFECTUE LA RECHERCHE D'UNE CHAINES DE CARACTERES
    Dim adr1 As String, c As Range, fini As Boolean
    If Id = "" Or Recherche = "" Then Lister2 = CVErr(xlErrValue): Exit Sub
    With Worksheets(1).Range(Recherche & "1:" & Recherche & "1500")
        Set c = .Find(Id, LookIn:=xlFormulas, lookat:=xlWhole)
        If c Is Nothing Then
            Lister2 = CVErr(xlErrNA): Exit Function
        Else
            adr1 = c.Address
            Do
                Lister2 = Lister2 & ", " & Worksheets(1).Cells(c.Row, Trouver)
                Set c = .Find(Id, LookIn:=xlValues, lookat:=xlWhole, After:=c)
                If c Is Nothing Then fini = True Else fini = c.Address = adr1
            Loop Until fini
        End If
    End With
    Lister2 = Mid(Lister2, 3)
End Function

Je l'ai mise as variant pour pouvoir retourner une vraie valeur d'erreur #N/A

eric
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonsoir eriiic,
Je suis tombé sur les mêmes constatations que toi. Je n'ai pas encore trouvé la solution pour corriger le problème alors je vais voir si ta proposition le résous. Merci à toi et bonne fin de soirée.
Amicales salutations.
Le Pingou
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Eriiic,
Pour ce qui est du test sur le loop, il y a un test juste après le find du début. Si c'est Nothing, on ne devrait pas exécuter la boucle...
J'ai changer la déclaration de la fonction, il y a du mieux, j'ai la première DRS, mais pas encore de liste...
Par contre, ta nouvelle fonction me retourne systématiquement l'erreur 2042 en debug ou #N/A sur la feuille?!
Je vais continuer à chercher.
Si vous avez d'autres idées, je suis preneur et surtout merci à vous deux pour votre aide.
Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
Bonjour,

Pour ce qui est du test sur le loop, il y a un test juste après le find du début. Si c'est Nothing, on ne devrait pas exécuter la boucle...
Relis ce que j'ai écrit.
La fonction que je te propose ne faisait pas cette erreur chez moi si on lui passe bien des paramètres Id er Recherche non vides, et chez le pingou non plus apparement...
2042=#N/A. De mémoire enlève Id="" and si tu ne veux pas cette erreur sur Id=""
Je re-contrôlerai ce midi, là je ne peux pas, on ne peut plus lire la fonction dans le post et je suis au boulot.
eric
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonsoir eriiic,
J'essaye ton code et j'ai une erreur de compilation : l'instruction Exit Sub n'est pas permise dans une fonction, je l'ai remplacé par Exit Function et là miracle tout est parfait.
Merci, j'ai eu grand plaisir à découvrir ta solution.
Amicales salutations
Le Pingou
Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
Ah oui, j'ai ajouté ça en dernière minute quand j'ai vu qu'il testait le contenu de Recherche. Trop vite et sans tester ;-)
D'ailleurs j'ai mis le test de Id en plus mais pas sûr que ce soit une bonne initiative. Retourner "" dans ce cas serait sans doute mieux. Il adaptera selon ses besoins.
Merci de la correction.
eric
Messages postés
23801
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
14 janvier 2021
6 566
La fonction avec les 2 petites modifications :
Public Function Lister2(ByVal Id As String, ByVal Recherche As String, Optional ByVal Trouver As String = "A") As Variant  ' EFFECTUE LA RECHERCHE D'UNE CHAINES DE CARACTERES
    Dim adr1 As String, c As Range, fini As Boolean
    If Recherche = "" Then Lister2 = CVErr(xlErrValue): Exit Function
    With Worksheets(1).Range(Recherche & "1:" & Recherche & "1500")
        Set c = .Find(Id, LookIn:=xlFormulas, lookat:=xlWhole)
        If c Is Nothing Then
            Lister2 = CVErr(xlErrNA): Exit Function
        Else
            adr1 = c.Address
            Do
                Lister2 = Lister2 & ", " & Worksheets(1).Cells(c.Row, Trouver)
                Set c = .Find(Id, LookIn:=xlValues, lookat:=xlWhole, After:=c)
                If c Is Nothing Then fini = True Else fini = c.Address = adr1
            Loop Until fini
        End If
    End With
    Lister2 = Mid(Lister2, 3)
End Function

Ca a l'air correct : https://www.cjoint.com/?DKvnRAMeXlL

Pourquoi enregistres-tu en xlsb et non pas en xlsm ?
eric

En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
34
Date d'inscription
mardi 3 juin 2008
Statut
Membre
Dernière intervention
28 mars 2016

Bonjour Eriiic,

Effectivement, ton fichier fonctionne nickel.

Je n'ai toujours pas compris pourquoi en mode debugg ceal fonctionne correctement et dans la feuille sa plante... La boucle .FindNext ne semble pas vouloir se faire!

Un grand merci à toi et à Le Pingou pour votre aide.
Messages postés
9823
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
28 décembre 2020
1 110
Bonjour maol,
Merci.
Comme l'avait précisé eriiic : sans doute dû à un bug de .findnext dans une fonction personnalisée.
J'ai eu le même problème chez moi en testant votre code. En fait elle fonctionne correctement si la valeur à trouver est un nombre (numérique) par contre avec du texte elle bug.
Cette fonction est un produit de MSO est nous ne pouvons pas la corrigée (pas d'accès au détail du code).
Utilisez donc la procédure d'eriiic.