Aide sur formules incomplètes ou substitution par VBA

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion Amilo
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
A

Amilo

XLDnaute Accro
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.
 
Trier par date Trier par votes
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
Cliquez pour agrandir...

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.
 
Vote positif 0 Vote négatif
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
 

Pièces jointes

Vote positif 0 Vote négatif
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
Cliquez pour agrandir...


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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...

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
Cliquez pour agrandir...

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.
 
Dernière édition:
Vote positif 0 Vote négatif
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
 
Dernière édition:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...

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.
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

M
[REF] Wiki 5 de MichelXld (Formules, Audits de formules, Répertoires, Fichiers)
Réponses
0
Affichages
47 K
MichelXld
M
Retour