Aide sur formules incomplètes ou substitution par VBA

Amilo · 23 Septembre 2009

Bonsoir le forum,

Depuis hier soir, je me bats à trouver une formule de "recherche" pour remplir automatiquement ma colonne "montant ht" de l'onglet "devis" en fonction de l'onglet "code" (voir le fichier ci-joint) !!

La même formule "magique" (s'il y en a une !!!) sera ensuite appliquée à la colonne "Unité".

Pour l'heure, je bute sur 3 formules différentes dans mes tentatives. Ces formules sont reprises dans les zones de texte.

Pour les "champions" en VBA, une proposition par VBA sera la bien venue et sera peut être mieux adaptée que des formules !!!

J'espère que mes explications sont claires et vous remercie d'avance pour vos idées et propositions.

http://cjoint.com/?jxuU3fZmlg

Cordialement.

ROGER2327 · 24 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Bonsoir Amilo

Essayez

Code:

[COLOR="DarkSlateGray"]=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)[/COLOR]

en J15.

ROGER2327

Amilo · 24 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

ROGER2327 à dit:
Bonsoir Amilo
Essayez

Code:

[COLOR="DarkSlateGray"]=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)[/COLOR]

en J15.
ROGER2327

Bonjour le Forum, ROGER2327,

J'ai testé votre proposition et elle fonctionne trés bien.

Effectivement, il manquait quelques fonctions dans ma formule, je vais me pencher dessus pour mieux la comprendre !!

Mille merci en tout cas pour votre soutien.

En attendant peut-être une autre proposition d'un(e) pro en VBA pour ma formation personnelle.....

Bonne journée à tous.

Lii · 24 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Bonsoir

un exemple de ce qu'on peut faire par VBA avec une macro événementielle (et ce que j'ai compris).

ROGER2327 · 25 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Bonjour à tous

Une procédure événementielle dérivée de la formule

Code:

[COLOR="DarkSlateGray"]=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)[/COLOR]

Code à placer dans le module de la feuille "devis" :

Code:

[COLOR="DarkSlateGray"]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range, s As String
   If Not Intersect(Target, [A15:B19]) Is Nothing Then
      For Each oCel In Intersect(Target, [A15:B19]).Cells
         Application.EnableEvents = False
         Cells(oCel.Row, 3).Value = DE(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Cells(oCel.Row, 8).Value = QU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Cells(oCel.Row, 10).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Application.EnableEvents = True
      Next oCel
   End If
End Sub

Private Function PU(A As String, B As String)
   PU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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,2)")
   If IsError(PU) Then PU = ""
End Function

Private Function QU(A As String, B As String)
   QU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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)")
   If IsError(QU) Then QU = ""
End Function

Private Function DE(A As String, B As String)
   DE = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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,0)")
   If IsError(DE) Then DE = ""
End Function[/COLOR]

La colonne "Montant" est calculée par une formule.

ROGER2327

Amilo · 25 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

ROGER2327 à dit:

Bonjour à tous

Une procédure événementielle dérivée de la formule

Code:

[COLOR="DarkSlateGray"]=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)[/COLOR]

Code à placer dans le module de la feuille "devis" :

Code:

[COLOR="DarkSlateGray"]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range, s As String
   If Not Intersect(Target, [A15:B19]) Is Nothing Then
      For Each oCel In Intersect(Target, [A15:B19]).Cells
         Application.EnableEvents = False
         Cells(oCel.Row, 3).Value = DE(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Cells(oCel.Row, 8).Value = QU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Cells(oCel.Row, 10).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)
         Application.EnableEvents = True
      Next oCel
   End If
End Sub

Private Function PU(A As String, B As String)
   PU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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,2)")
   If IsError(PU) Then PU = ""
End Function

Private Function QU(A As String, B As String)
   QU = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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)")
   If IsError(QU) Then QU = ""
End Function

Private Function DE(A As String, B As String)
   DE = Evaluate("=OFFSET(INDEX(Code!$A$1:$P$11,MATCH(""" & B & """,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,0)")
   If IsError(DE) Then DE = ""
End Function[/COLOR]

La colonne "Montant" est calculée par une formule.

ROGER2327

Bonjour le Forum, Lii, Robert2327,

Merci beaucoup Lii pour ce progamme VBA et ses commentaires, c’est exactement le résultat souhaité. Vivement que j’apprenne VBA, c’est très pratique mais assez difficile.

Pour l’instant je suis encore au stade des formules où je bloque encore parfois.

Je me suis penché sur la formule à Roger que j’ai reprise ci-dessous,

=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)

De manière générale nous avons la fonction « decaler » avec 3 arguments :
=decaler(point de départ ;position ligne vers le bas ;position colonne à droite)

qui donne =decaler(0 ;0 ;2)

j’ai compris dans l’ensemble mais accroche sur le 1er argument :

INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0))

Je creuse actuellement un peu plus sur cette fonction pour essayer de bien la comprendre… mais ça viendra…. !!!

Un grand merci encore à Roger pour son code VBA, j’ai à présent tout ce qui me faut pour bien avancer.

Pour les VBA, je me laisse encore «beaucoup de temps » pour les comprendre parfaitement !!!!.

Mille mercis

Bonne journée à tous

Amilo · 27 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Amilo à dit:
Bonjour le Forum, Lii, Robert2327,

Merci beaucoup Lii pour ce progamme VBA et ses commentaires, c’est exactement le résultat souhaité. Vivement que j’apprenne VBA, c’est très pratique mais assez difficile.

Pour l’instant je suis encore au stade des formules où je bloque encore parfois.

Je me suis penché sur la formule à Roger que j’ai reprise ci-dessous,

=DECALER(INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0));0;2)

De manière générale nous avons la fonction « decaler » avec 3 arguments :
=decaler(point de départ ;position ligne vers le bas ;position colonne à droite)

qui donne =decaler(0 ;0 ;2)

j’ai compris dans l’ensemble mais accroche sur le 1er argument :

INDEX(source;EQUIV(B15;DECALER(Code!$A$1:$A$11;0;EQUIV(A15;liste;0)-2);0);EQUIV(A15;liste;0))

Je creuse actuellement un peu plus sur cette fonction pour essayer de bien la comprendre… mais ça viendra…. !!!

Un grand merci encore à Roger pour son code VBA, j’ai à présent tout ce qui me faut pour bien avancer.

Pour les VBA, je me laisse encore «beaucoup de temps » pour les comprendre parfaitement !!!!.

Mille mercis

Bonne journée à tous

Bonsoir le Forum, bonsoir Roger2327, bonsoir Lii,

Je suis vraiment navré de revenir sur ce fil mais j'ai apporté 2 petites modifications dans mon tableau initial et du coup les codes VBA que vous m'aviez proposés ne fonctionnent plus.
Les modifications sont expliquées dans le fichier ci-joint.

J'ai commencé à me pencher sur le code à Roger2327 toute la journée pour l'adapter aux modifications mais sans succés.

Je pense que le rajout d'une colonne fait que la ligne ci-dessous n'est plus juste sans citer les routines par rapport aux nouvelles formules :

"For Each oCel In Intersect(Target, [A24:B47]).Cells"

Je bloque également sur la compréhension de la ligne suivante, notamment :
"Cells(oCel.Row, 11).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 2).Value)"

Pouvez-vous svp m'aider à rétablir le code VBA par rapport au nouveau tableau !!!

Merci d'avance

http://cjoint.com/?jBwVcm3HSO

Bonne nuit à tous.

ROGER2327 · 28 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Re...

Essayez :

Code:

[COLOR="DarkSlateGray"]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range, s As String
   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
         If IsError(Cells(oCel.Row, 3).Value) Then
            Cells(oCel.Row, 4).Value = Empty
            Cells(oCel.Row, 9).Value = Empty
            Cells(oCel.Row, 11).Value = Empty
         Else
            Cells(oCel.Row, 9).Value = QU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 3).Value)
            Cells(oCel.Row, 11).Value = PU(Cells(oCel.Row, 1).Value, Cells(oCel.Row, 3).Value)
         End If
         Application.EnableEvents = True
      Next oCel
   End If
End Sub[/COLOR]

La fonction DE n'est plus utilisée.

Bon courage.
ROGER2327

Amilo · 28 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Bonsoir le Forum, bonsoir Roger2327,

Malgré votre précédente proposition, je n'arrive pas à l'adapter à mon nouveau tableau.

J'ai indiqué les formules Excel que j'ai oubliées dans mon précédent tableau. Je vous le joins un autre avec les formules que je souhaite remplacer par VBA pour le "Code", "Unité" et le "PU"

Elles se trouvent notammant sur la ligne 16 et je les ai intégrées dans VBA également.

J'ai également placé des commentaires dans les codes VBA.
http://cjoint.com/?jCty3WwaXD

Merci d'avance pour votre soutien.

Cordialement

ROGER2327 · 29 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

Re...

La variable x n'est pas utilisée : il faut la supprimer. (J'ai corrigé mon précédent message.)
Pour le reste, je ne comprends pas grand chose.
Une fonction CO est appelée mais n'est écrite nulle part.
Les fonctions PU et QU font appel aux paramètres A et B, mais ces paramètres ne sont pas utilisés par les fonctions.
La fonction DE n'est jamais appelée par Sub Worksheet_Change. A quoi sert-elle ?
Ça devient vraiment incompréhensible pour moi. Désolé.

ROGER2327

Amilo · 29 Septembre 2009

Re : Aide sur formules incomplètes ou substitution par VBA

ROGER2327 à dit:
Re...
La variable x n'est pas utilisée : il faut la supprimer. (J'ai corrigé mon précédent message.)
Pour le reste, je ne comprends pas grand chose.
Une fonction CO est appelée mais n'est écrite nulle part.
Les fonctions PU et QU font appel aux paramètres A et B, mais ces paramètres ne sont pas utilisés par les fonctions.
La fonction DE n'est jamais appelée par Sub Worksheet_Change. A quoi sert-elle ?
Ça devient vraiment incompréhensible pour moi. Désolé.
ROGER2327

Bonjour Roger2327, bonjour le Forum,

Comme j'ai beaucoup de mal avec VBA, j'ai recopié bêtement les formules dans les fonctions PU, QU, et CO. Ces fonctions représentent les colonnes à renseigner en VBA.

Vous aviez "super bien" réalisé la macro dans mon tableau intial de début du fil pour les intitulés "Désination", "Unité" et "Prix unitaire".

Comme j'ai modifié mon tableau (voir le dernier lien) et certaines formules Excel, j'aurais souhaité la même chose en vba mais pour les intitulés "Code", "Unité" et "Prix unitaire" cette fois-ci.

Pour faire simple, ne tenez pas compte de ce que j'ai indiqué ou rajouté dans votre macro, car je suis vraiment débutant en vba.

En vous remerciant, j'epère vous avoir apporté plus de précisions !!

Cordialement.

Aide sur formules incomplètes ou substitution par VBA

Amilo

XLDnaute Accro

ROGER2327

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Lii

XLDnaute Impliqué

Pièces jointes

ROGER2327

XLDnaute Barbatruc

Pièces jointes

Amilo

XLDnaute Accro

Amilo

XLDnaute Accro

ROGER2327

XLDnaute Barbatruc

Amilo

XLDnaute Accro

ROGER2327

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Discussions similaires

Aide sur formules incomplètes ou substitution par VBA

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Discussions similaires

Privacy & Transparency

Privacy & Transparency