[Résolu] Erreur dans le Code VBA Excel

[Armel]

Bonsoir le Forum,

 

J'espère que je suis dans la bonne rubrique et mais j'ai un problème dans le code du fichier ci-joint.

 

Je souhaite obtenir une recherche de valeurs par VBA (vous trouverez en commentaire l'équivalent de la formule Excel dans le code VBA).

 

Les valeurs recherchées automatiquement sont dans les colonnes en VERT (colonnes C, I, K) en fonction des valeurs sélectionnées par validation de données en colonnnes JAUNE (colonne A et D).

 

Pouvez-vous svp m'aider sur ce code que je n'arrive pas à résoudre ?

 

Merci d'avance de votre soutien.

 

 

Mon fichier Excel

Cordialement.

Modifié le 6 novembre 2009 par Armel

[Wullfk]

Bonsoir,

 

un très bon site avec son forum pour tous ce qui concerne Excel : ExcelDownload il mon dépanner plus d'une fois sur des formules et autres fonctionnalités d'excel.

 

Salut

[Armel]

Bonsoir Wullfk,

 

Oui effectivement, je connais trés bien Excel download qui est super et j'y suis insrit depuis quelque temps déjà.

 

J'avais déjà posé ce problème une première fois et j'ai reçu 2 solutions VBA trés intéressantes qui fonctionnaient sans problème.

 

J'ai malheureusement modifié mon tableau initial en rajoutant une colonne vide entre les 2 premières colonnes pour la mise en forme réelle.

 

J'ai tenté d'abord de modifier et d'adapter la macro à ce nouveau tableau mais sans succés rendant ainsi la macro incompréhensible.

 

Je n'ai pas insisté pour solliciter d'avantage leur aide qui je pense re-demande pas mal d'effort et de travail.

 

Voilà, je me suis dit que peut-être je trouverai mon bonheur sur Zebulon.

 

Merci comme même de votre réponse.

 

Bonne soirée à tous

[Lupin]

Bonjour,

 

J'ai regardé le code se trouvant derrière la feuille [Feuil1(devis)].

 

Vous avez oublié le signe [=] dans cette troisième "colonne" de recherche.

 

J'ai déclaré tout le code de façon explicite, ça donne ceci :

 

Option Explicit

'

 

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim oCel As Range, s As String, DE As Range, QU As Range, PU As Range

 

If Not Intersect(Target, Union([A15:A19], [D15:D19])) Is Nothing Then

For Each oCel In Intersect(Target, Union([A15:A19], [D15:D19])).Cells

Application.EnableEvents = False

Cells(oCel.Row, 3).Value = DE(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Cells(oCel.Row, 9).Value = QU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Cells(oCel.Row, 11).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Application.EnableEvents = True

Next oCel

End If

 

End Sub

'

 

' La fonction retourne une valeur non défini, donc de Type [Variant]

' et les arguments typé n'étant pas déclaré By... sont implicitement [byVal]

' pour retourner une valeur modifié, utiliser ByRef

Private Function PU(ByVal A As String, ByVal B As String, ByVal C As String, ByVal D As String) As Variant

 

PU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

D & """,OFFSET(Code!$B$1:$B$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-2),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,2)")

 

'DECALER(INDEX(source;EQUIV(D17;DECALER(Code!B:B;0;EQUIV(A17;liste;0)-2);0);EQUIV(A17;liste;0));0;2)

 

If IsError(PU) Then PU = ""

 

End Function

'

 

' La fonction retourne une valeur non défini, donc de Type [Variant]

' et les arguments typé n'étant pas déclaré By... sont implicitement [byVal]

Private Function QU(ByVal A As String, ByVal B As String, ByVal C As String) As Variant

 

QU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

C & """,OFFSET(Code!$A$1:$A$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-2),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,1)")

 

'DECALER(INDEX(Source;EQUIV(C26;DECALER(Depart;0;EQUIV(A26;Designation;0)-2);0);EQUIV(A26;Designation;0));0;1))

 

If IsError(QU) Then QU = ""

 

End Function

'

 

' La fonction retourne une valeur non défini, donc de Type [Variant]

' et les arguments typé n'étant pas déclaré By... sont implicitement [byVal]

Private Function DE(ByVal A As String, ByVal B As String, ByVal C As String) As Variant

 

DE = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

C & """,OFFSET(Code!$A$1:$A$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-1),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,-1)")

 

'DECALER(INDEX(Source;EQUIV(C24;DECALER(Depart;0;EQUIV(A24;Designation;0)-1);0);EQUIV(A24;Designation;0));0;-1))''

 

If IsError(DE) Then DE = ""

 

End Function

 

Bonne continuité

 

Lupin

Modifié le 14 octobre 2009 par Lupin

[Armel]

Bonsoir Lupin,

 

Merci d'avoir analysé et modifié ce code. Je viens de l'essayer et j'ai à présent le message suivant "Erreur d'exécution 91".

Je ne sais pas ce qu'il signifie mais je soupçonne une erreur due à un copier/coller !!

 

Je vous joins le nouveau fichier avec le code modifié.

 

Pouvez-vous svp vérifier ce code à nouveau.

 

http://cjoint.com/?kovUtZOafP

 

Merci d'avance pour votre aide

 

Slts

[Lupin]

Bonjour,

 

J'ai manqué le nombres de paramètres dans ma première observation.

 

Vous appelez les fonctions en passant 2 paramètres :

 

Cells(oCel.Row, 3).Value = DE(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Cells(oCel.Row, 3).Value = DE( Paramètre1 , Paramètre2 )

 

Et à la déclaration, vous demandé 3 paramètres en entrée :

Private Function DE(ByVal A As String, ByVal B As String, ByVal C As String) As Variant

Private Function DE(ByVal Paramètre1 As String, ByVal Paramètre2 As String, ByVal Paramètre3 As String) As Variant

 

Soit vous placer le 3ième paramètre Optionel :

Private Function DE(ByVal A As String, ByVal B As String, Optional ByVal c As String) As Variant

 

Ou trouver le troisième paramètre à inclure :

 

Cells(oCel.Row, 3).Value = DE( Paramètre1 , Paramètre2 , Paramètre3 )

 

Il en est de même pour la fonction PU qui elle possède 4 paramètres alors que seulement 2 lui sont passés !

 

Je n'ai pas encore tenté de comprendre la syntaxe de la formule Excel, quoique je connaisse bien VBA,

je connais mal les fonctions d'Excel.

 

Je regarderai plus tard pour la formule, là c'est l'heure de roupiller

 

Bonne continuité.

 

Lupin

Modifié le 15 octobre 2009 par Lupin

[Armel]

Bonjour le Forum, Bonjour Lupin,

 

Merci d'avoir répondu à mon message, je suis débutant en VBA et je n'ai jamais compris tout le sens de ce code. Je vais lire attentivement vos explications qui je l'espère m'aideront à comprendre un peu plus la structure du programme.

 

Pour info, comme vous l'aviez constaté, j'ai corrigé le code dans mon propre fichier en rajoutant le "=" manquant alors qu'il est toujours manquant dans le précédent fichier du lien.

 

Merci encore pour votre aide

 

Cordialement

[Lupin]

Bonjour,

 

Je suis plutôt perplexe face à tout ça !

 

C'est le première fois, que je vois dans un même

module une déclaration de variable locale et de fonction

portant le même nom ! Je n'ai certe pas tout compris !

 

Le code VBA est en soi inutile, les fonctions complexes

sont suffisantes pour remplir les colonnes souhaitées :

 

En [ C15 ], copier cette formule :

=DECALER(INDEX(Code!$A$1:$P$11;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;Code!$A$1:$P$1;0)-2);0);EQUIV(A15;Code!$A$1:$P$1;0));0;-1)

Tirer la formule vers le bas

 

En [ I15 ], copier cette formule :

=DECALER(INDEX(Code!$A$1:$P$11;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;Code!$A$1:$P$1;0)-2);0);EQUIV(A15;C

ode!$A$1:$P$1;0));0;1)

Tirer la formule vers le bas

 

En [ K15 ], copier cette formule :

=DECALER(INDEX(Code!$A$1:$P$11;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;Code!$A$1:$P$1;0)-2);0);EQUIV(A15;C

ode!$A$1:$P$1;0));0;2)

Tirer la formule vers le bas

 

Et le code qui fonctionne avec, si vous tenez à l'utiliser :

Malheureusement, la tabulation sera détruite !

 

Option Explicit

'

 

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim oCel As Range, s As String, DE As Range, QU As Range, PU As Range

 

If Not Intersect(Target, Union([A15:A19], [D15:D19])) Is Nothing Then

For Each oCel In Intersect(Target, Union([A15:A19], [D15:D19]))

Application.EnableEvents = False

Cells(oCel.Row, 3).Value = DE(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Cells(oCel.Row, 9).Value = QU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Cells(oCel.Row, 11).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)

Application.EnableEvents = True

Next oCel

End If

End Sub

'

 

Private Function PU(ByVal A As String, ByVal B As String, ByVal c As String, ByVal D As String) As Variant

PU = Evaluate("OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

D & """,OFFSET(Code!$B$1:$B$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-2),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,2)")

 

'DECALER(INDEX(source;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)

If IsError(PU) Then PU = ""

End Function

 

Private Function QU(ByVal A As String, ByVal B As String, ByVal c As String) As Variant

QU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

c & """,OFFSET(Code!$A$1:$A$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-2),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,1)")

'DECALER(INDEX(Source;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;1))

If IsError(QU) Then QU = ""

End Function

 

Private Function DE(ByVal A As String, ByVal B As String, Optional ByVal c As String) As Variant

DE = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & _

c & """,OFFSET(Code!$A$1:$A$11,0,MATCH(""" & _

A & """,Code!$A$1:$P$1,0)-1),0),MATCH(""" & _

A & """,Code!$A$1:$P$1,0)),0,-1)")

'DECALER(INDEX(Source;EQUIV(D15;DECALER(Code!B:B;0;EQUIV(A15;liste;0)-1);0);EQUIV(A15;liste;0));0;-1))''

If IsError(DE) Then DE = ""

End Function

 

En fait le dernier chiffre de l'expression agit d'offset par rapport à la colonne du nom du produit de la feuille

 

+1 nous donne l'unité

+2 nous donne le prix unitaire

-1 nous donne le code

 

Bonne continuité

 

Lupin

Modifié le 17 octobre 2009 par Lupin

[Armel]

Bonjour le Forum, bonjour Lupin,

 

Je viens de lire votre nouvelle réponse et je vous en remercie.

 

Il est vrai que les formules Excel fonctionnent très bien, et je n'ai personnellement aucun souci avec, mais j'ai préféré le VBA car c'est mon frère qui utilisera ce fichier et il ne connaît rien en formules Excel. Et je crains qu'il mette la pagaille en supprimant les formules en voulant écraser par exemple les prix unitaires qui ne lui conviendront pas !

 

Aussi, je tiens beaucoup à VBA, mais j'ai testé le nouveau code et contrairement à ce que vous indiquez, chez moi j'ai toujours le même message d'erreur :

"Erreur d'exécution 91, Variable objet ou variable de bloc With non définie"

 

A priori cela fonctionne chez vous quand vous dites ceci :

 

Et le code qui fonctionne avec, si vous tenez à l'utiliser :

Malheureusement, la tabulation sera détruite

Pouvez-vous SVP me transmettre le fichier avec le code VBA qui fonctionne ?

 

C'est peut-être trop vous demander, mais je pense que vous auriez proposé un tout autre code si je n'avais mis le code actuel. N'aurait-il pas été plus simple pour vous de me faire une toute autre proposition comme vous maîtrisez bien VBA ?

 

Si vous trouvez que cela est trop compliqué, je préfère continuer sur l'actuel...

 

Je vous joins le tout premier fichier avec le code VBA qui fonctionne (mis dans le forum Excel download) et je trouve surprenant qu'ici tout fonctionne et que dans le nouveau tableau avec une colonne en plus, ça coince mais il est vrai avec des formules de "recherche" un peu différentes !

 

Peut-être qu'en vous inspirant de ce code, il vous sera plus simple de trouver l'erreur s'il y en a une !

 

http://cjoint.com/?krtIfHlbGS

 

Merci d'avance en tout cas pour tout le travail et toute la peine que vous vous êtes donné jusqu'à maintenant.

 

Bonne soirée à tous.

[Dylav]

Armel, lorsque tu réponds au post juste précédent, pourquoi le répéter en citation, puisque le lecteur l'a déjà naturellement sous les yeux ? Au bouton préfère le bouton qui est juste au-dessous