XL 2010 VBA - Décalage tableau + Pb valeur #N/A dans VLOOKUP

[excelnoob]

Bonjour à tous,


Je fais appel à votre gentillesse et vos compétences pour m'aider à résoudre 2 problèmes dans un fichier que j'ai récupéré (fichier attaché avec les macros inclues)


Les 2 problèmes sont les suivants :

1) Pour le tableau dans l'onglet "extract supplies", lorsque j'applique la macro "Extraire_Supplies" (dans l'onglet Preleq bouton "extraction des suppliers") , un décalage des lignes de la dernière colonne apparait (mis en évidence en orange) --> macro associée "Extraire_Supplies"

2) Pour le tableau dans l'onglet "analyse des consommables", lorsque une valeur n'est pas retrouvée selon les formules en colonne E et F , la valeur #N/A apparaît : Ici je souhaiterais qu'à la place il apparaisse la valeur 0 à la place --> macro associée "Bouton_Control"


Principe du fonctionnement du tableau:
- Intégration de la liste analysée mise dans l'onglet "Prelreq"
- dans l'onglet PrelReq : bouton "Mise en forme du Preleq" puis bouton "extraction des supplies"
- Apparition de l'onglet "Extract supplies" --> le 1er problème apparait (décalage des lignes de la dernière colonne)
- Onglet "Analyse des Consommables", bouton "contrôler le Prelreq" --> le 2ème défaut apparait (valeur non retrouvée apparait en #N/A)
- Bouton "Lancer un nouveau contrôle..": reinit du fichier

Je tiens d'avance à vous remercier pour votre aide.

 

[lupin]

Bonjour,
Je viens de regarder le fichier et apporté quelques modification dans les macros pour les alléger et apparemment pas de souci de décalage lors de la macro "Extraire_Supplies". Juste pour info il est inutile de réer des colonnes pour les formules TRIM et ensuite copier et supprimer les colonnes. Tout cela peut se faire directement avec la macro (voir code).
D'autre part pour éviter le message #NA il suffit d'utiliser la fonction IFERROR (formule;"").
Si la formule contenue dans la fonction retourne une erreur il n'affichera rien dans l'exemple, sinon il renvoie le résultat.
Voici le fichier en retour ...
J'espère avoir pu aider et répondre aux problèmes.

 

[excelnoob]

effectivement cela correspond à ma demande, je tiens à te remercier.

Question subsidiaire et dernière pour ce dossier: comment faire une recherche selon doubles conditions sous VBA

Si par exemple je veux qu'apparaisse dans l'onglet "Analyse des Consommables" en colonne I , les "Consommables ".
Selon la condition par exemple :
si la valeur en colonne F (de l'onglet "Analyse des Consommables") est retrouvé à l'identique en colonne C (de l'onglet "Rappel Liste de Référence) et que je trouve aussi en colonne B (de l'onglet "Rappel Liste de Référence) la valeur "A777" (sur la même ligne de recherche) --> alors résultat "Consommable" (mis dans la colonne I de l'onglet "Analyse des Consommables")

Merci d'avance pour l'aide apportée

 

[lupin]

Voilà, c'est une autre formule car la valeur à contrôler est en dehors du lookup. Il faut donc utiliser INDEX et MATCH.
La formule est donc
=IFERROR(IF(INDEX('Rappel Liste référence'!B:B;MATCH('Extract SUPPLIES'!E8;'Rappel Liste référence'!C:C;0))="A777";"Consommable";"");"")

Et en VBA cela donne
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(INDEX('Rappel Liste référence'!C[-4],MATCH('Extract SUPPLIES'!R[-3]C[-1],'Rappel Liste référence'!C[-3],0))=""A777"",""Consommable"",""""),"""")"

Evidemment je n'ai pas prévu de retour de valeur si on ne trouve pas "A777" !
Bonne journée

 

[excelnoob]

Bonjour,

Je peux me tromper mais j'ai l'impression après test que ça ne correspond pas exactement à mon besoin (ou alors je n'arrive pas à l'utiliser)

Ce que je recherche exactement si je reformule c'est :

déjà du tableau "Analyse des consommables je pars de la colonne I pour la formule : Range("i5").Select)

Soit
Range("i5").Select
ActiveCell.FormulaR1C1 = _

et la formule recherchée :
- Si la valeur dans la "colonne F" (onglet "analyse des consommables") est retrouvée dans "colonne C" (Onglet "Liste de référence")
- et que en plus dans la "colonne B" (Onglet "Liste de référence") on trouve la valeur "A777"
- Alors Afficher "Consommable" dans la colonne I (onglet "Analyse des Consommables")


Avec pour rappel : la ligne de recherche part de la colonne F et que le résultat en colonne I sera sur la même ligne

Merci d'avance pour ton aide

 

[excelnoob]

j'ai après un grand nombre de tests fait cette formule pour plusieurs conditions :

Mais seul les 2 ères conditions "Elément connu : OK" et "Consommable OK" sont pris correctement sinon ça va directement à "". je ne vois pas d'où vient l'erreur ...


"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH('Analyse des Consommables'!C[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")"

 

[lupin]

Bonjour,

Je peux me tromper mais j'ai l'impression après test que ça ne correspond pas exactement à mon besoin (ou alors je n'arrive pas à l'utiliser)

Ce que je recherche exactement si je reformule c'est :

déjà du tableau "Analyse des consommables je pars de la colonne I pour la formule : Range("i5").Select)

Soit
Range("i5").Select
ActiveCell.FormulaR1C1 = _

et la formule recherchée :
- Si la valeur dans la "colonne F" (onglet "analyse des consommables") est retrouvée dans "colonne C" (Onglet "Liste de référence")
- et que en plus dans la "colonne B" (Onglet "Liste de référence") on trouve la valeur "A777"
- Alors Afficher "Consommable" dans la colonne I (onglet "Analyse des Consommables")


Avec pour rappel : la ligne de recherche part de la colonne F et que le résultat en colonne I sera sur la même ligne

Merci d'avance pour ton aide

***************************************
Désolé mais en effet, trop rapide dans ma lecture et j'ai confondu les colonnes.
Dans la colonne F (cellule F5) la formule est :
=IFERROR(VLOOKUP(C5;'Rappel Liste référence'!A:A;1;FALSE);"")

Traduction en VBA :
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],'Rappel Liste référence'!C[-4],1,False),"""")"

Dans la colonne I (cellule I5) la formule est :
=IFERROR(IF(INDEX('Rappel Liste référence'!B:B;MATCH(F5;'Rappel Liste référence'!C:C;0))="A777";"Consommable";"");"")

Traduction en VBA :
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(INDEX('Rappel Liste référence'!C[-7],MATCH(RC[-3],'Rappel Liste référence'!C[-6],0))=""A777"",""Consommable"",""""),"""")"

Cordialement

 

[lupin]

j'ai après un grand nombre de tests fait cette formule pour plusieurs conditions :

Mais seul les 2 ères conditions "Elément connu : OK" et "Consommable OK" sont pris correctement sinon ça va directement à "". je ne vois pas d'où vient l'erreur ...


"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH('Analyse des Consommables'!C[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")"

-------------------------------------------------------

Voici la correction :
=IFERROR(IF(INDEX('Rappel Liste référence'!C[-7],MATCH(RC[-3],'Rappel Liste référence'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")"

Avec ce code cela fonctionne bien.

 

[excelnoob]

j'ai testé ta dernière formule et j'ai toujours le même problème cela prend en compte Consommable OK et element connu mais pas les autres.

Pourtant comme toi, j'ai l'impression que ça devrait fonctionner

 

[excelnoob]

si je résume :

- la fomule seule me trouve bien "concommable ok" ou vide
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",""""),"""")

- la formule me trouve bien "Elément connu", "DTR manquant mais libellé connu", "DTR connu : Problème libellé", "Elément non connu à Analyser"
"=IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))


Mais si j'utilise cette formule ne reconnait que Consommable et élément connu

"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")


Là je ne comprends pas du tout pourquoi la dernière formule ne fonctionne pas correctement.

 

[excelnoob]

bon je viens de trouver d'où vient le problème , mais pour l'instant je ne vois pas la solution pour le régler.

la formule ci-dessous ne fonctionne pas correctement: si j'ai RC[-3] vide.
Pour tester j'ai mis toto à la place de """" et il s'évère que toutes les lignes donc RC[-3] sont vides ç a donne un résultat vide.
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",""TOTO""),"""")



et du coup, je comprends encore moins que ça affecte autant cette formule : qui ne détecte que Consommable ok et Elément connu
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")

il faudrait si je comprends bien que la formule ci-dessus dise, que si on respecte l'index on a un "consommable ok" sauf si RC[-3] est vide dans ce cas voir les autres conditions aussi. Et si index non respecté idem voir les autres conditions.

 

[lupin]

bon je viens de trouver d'où vient le problème , mais pour l'instant je ne vois pas la solution pour le régler.

la formule ci-dessous ne fonctionne pas correctement: si j'ai RC[-3] vide.
Pour tester j'ai mis toto à la place de """" et il s'évère que toutes les lignes donc RC[-3] sont vides ç a donne un résultat vide.
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",""TOTO""),"""")



et du coup, je comprends encore moins que ça affecte autant cette formule : qui ne détecte que Consommable ok et Elément connu
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),"""")

il faudrait si je comprends bien que la formule ci-dessus dise, que si on respecte l'index on a un "consommable ok" sauf si RC[-3] est vide dans ce cas voir les autres conditions aussi. Et si index non respecté idem voir les autres conditions.

 

[lupin]

Serait-il possible d'avoir un fichier avec des données plus diversifiées ? Avec le contenu actuel c'est difficile de voir où et comment solutionner.

 

[lupin]

Apparemment une mauvaise manipulation dans ma réponse, désolé.
Bon j'essaie de résumer pour que nous y voyons clair.
Si RC[-3] est vide la formule doit retourner #NA (logique valeur non existante dans la feuille Rappel liste ...) mais reste vide vu la fonction IFERROR empêchant l'affichage de l'erreur !
En examinant les données, voici mon cheminement.
Si RC[-3] est vide en RC[-2] (soit colonne G) affichage de ID NOK et en RC[-1] (soit colonne H) affichage de Désignation NOK. Donc nous nous trouvons dans les dernières conditions de la formule et l'affichage devrait être Elément non connu: A ANALYSER !
La formule devrait donc être :
"=IFERROR(IF(INDEX('Rappel Liste CITADIS'!C[-7],MATCH(RC[-3],'Rappel Liste CITADIS'!C[-6],0))=""Commerce"",""Consommable OK"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation OK""),""Elément connu: OK"",IF(AND(RC[-2]=""DTR NOK"",RC[-1]=""Désignation OK""),""DTR manquant mais libellé connu"",IF(AND(RC[-2]=""DTR OK"",RC[-1]=""Désignation NOK""),""DTR connu: Problème libellé"",""Elément non connu: A ANALYSER"")))),""Elément non connu: A ANALYSER"")
Changer le dernier élément en répétant le résultat des dernières conditions remplies.

Je crois que cette fois ce doit être la bonne.
Cdlt

 

[excelnoob]

Salut Lupin,

on est bien sur la même longueur d'onde sauf que pour une raison dont j'ignore certaines conditions ne fonctionnent pas comme je te le disais plus haut.

Les 2 conditions non prises en compte sont (il passe directement à : Elément non connu: A ANALYSER)

DTR OK Désign NOK DTR connu: Problème libellé
DTR NOK Désign OK : DTR manquant mais libellé connu

Effectivement le mieux est que je te communique un fichier qui fonctionne (mais bidouillé et non fiable) et le fichier avec la formule en cours qui ne fonctionne pas.

As-tu un lien ou je peux te télécharger les 2 fichiers, tout en gardant les données privées ?