Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

[Amilo]

Bonsoir le forum,

Comme il est impossible de résoudre ce cas par formules, enfin d'après moi, pouvez-vous svp m'aider pour un code en VBA si ce n'est pas trop compliqué...,

Je vous transmets un fichier simplifié en pièce jointe pour l'exemple,

En saisissant un nombre dans une des 3 cellules (Mt HT, ou TVA, ou Mt TTC), je souhaiterais que les 2 autres cellules se calculent automatiquement selon le critère Code TVA indiqué dans la colonne M,

Et ceci pour toute saisie dans la plage définie en couleur jaune,

Pour l'exemple, j'ai indiqué à chaque fois le nombre 10,

P.S : j'ai indiqué les codes de TVA et leur taux respectif à côté de ma plage mais en réalité je pense qu'ils seront dans une autre feuille du même classeur,
je ne sais pas s'il y a besoin de faire référence à cette plage de codes...??

Merci d'avant pour votre aide

Cordialement

 

[jpb388]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonsoir à tous
en i7 et tirer vers le bas

=H7*RECHERCHEV(M7;$P$7:$Q$14;2)

 

[ROGER2327]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonsoir à tous

Une autre :​

=H7*INDEX($Q$7:$Q$14;EQUIV(M7;$P$7:$P$14))

en I7, à recopier vers le bas.​

ROGER2327
#6123

Mardi 3 Tatane 139 (Sainte Crapule, puriste et Saint Fantomas, archange - fête Suprême Quarte)
28 Messidor An CCXX, 9,4274h - vesce
2012-W29-1T22:37:33Z

 

[Amilo]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonjour jpb388, Roger2327,

Merci beaucoup pour vos solutions, les 2 formules sont intéréssantes et me serviront j'en suis sur un jour dans d'autres situations,

Mais mon problème est que je peux être amené à saisir, selon la situation, l'une des 3 cellules aussi bien le montant de la TVA directement ou une des 2 autres cellules Mt HT ou Mt TTC...!!

Dans ce cas avec une formule, j"écraserais celle-ci en voulant saisir directement le montant de la TVA...

Merci encore à vous,

Cordialement

 

[jpb388]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonjour à tous
nous avions mal lu le titre

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Taux&, Lg&
If Target.Column = 8 Or Target.Column = 13 Or Target.Column = 12 Then
On Error GoTo Erreur
Lg = Range("p7:q14").Find(Range("m" & Target.Row).Text).Row
Select Case Target.Column
    Case 8
        Range("i" & Target.Row).FormulaR1C1 = "=R[-11]C[-6]*VLOOKUP(R[-11]C[-1],R7C16:R14C17,2,FALSE)"
        Range("L" & Target.Row) = Range("h" & Target.Row) + Range("i" & Target.Row)
    Case 13
        Range("i" & Target.Row).FormulaR1C1 = "=R[-11]C[-6]*VLOOKUP(R[-11]C[-1],R7C16:R14C17,2,FALSE)"
        Range("L" & Target.Row) = Range("h" & Target.Row) + Range("i" & Target.Row)
        Range("H" & Target.Row) = Range("L" & Target.Row) - Range("I" & Target.Row)
    Case 12
        Range("i" & Target.Row).FormulaR1C1 = "=R[-11]C[-6]*VLOOKUP(R[-11]C[-1],R7C16:R14C17,2,FALSE)"
        Range("H" & Target.Row) = Range("L" & Target.Row) - Range("I" & Target.Row)
End Select
End If
Erreur:
End Sub

 

[Amilo]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Rebonjour jpb388,

Merci beaucoup de vous être penché sur une solution en VBA,

Après test, celle-ci ne fonctionne malheureusement pas, cela renvoie une erreur #N/A,

Je ne sais pas si c'est moi qui ai oublié une étape...ou bien une erreur s'est glissée dans le code...?

Je joins à nouveau le fichier avec le code, je vais essayer de chercher un peu par moi même...

Merci encore pour votre aide

Cordialement

 

[ROGER2327]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonsoir à tous

Un essai dans le classeur joint, en espérant avoir bien lu la question...​

ROGER2327
#6130

Mercredi 4 Tatane 139 (Ascension du Mouchard, statisticien, psychiatre et policier - fête Suprême Quarte)
29 Messidor An CCXX, 4,1501h - blé
2012-W29-2T09:57:37Z

 

[Amilo]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonsoir Roger2327,

C'est génial, ça correspond exactement au résultat souhaité,

Côté instructions VBA, ça ressemble à du code de pro....

Mille mercis encore à vous,

Bonne soirée

Cordialement

 

[Amilo]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonjour le Forum, Roger2327,

J'aurais juste 1 ou 2 ptites questions sur le code à Roger2327 que je trouve trés bien mais également une ptite modification à apporter,

1. Ma 1ère question : Est-ce que le & dans la variable i ci-dessous est necéssaire et à quoi il sert ??

Private Sub Worksheet_Change(ByVal Cible As Range)
Dim i&, Cel As Range, CTva As Range, Plg As Range, Dat As Range, tmp

2. L'autre question : Est-ce que l'argument Cible dans la 1ère ligne est optionnel et à quoi il sert dans les 2 lignes de code ??

Private Sub Worksheet_Change(ByVal Cible As Range)
Dim i&, Cel As Range, CTva As Range, Plg As Range, Dat As Range, tmp
On Error GoTo E
Set CTva = Range("P7:Q14") 'Tableau des codes de TVA
Set Plg = Range("H7:M35") 'Plage de calcul
Set Dat = Intersect(Plg, Cible) 'Cellules à traiter

3. Concernant la ptite modification dans le code, j'ai indiqué les explications dans le fichier joint,

Merci d'avance

Cordialement

 

[ROGER2327]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonjour Amilo

1. L'esperluète "&" (dont certains attribuent la paternité au secrétaire du regretté Cicéron , mec au demeurant assez carré malgré son nom, mais je m'égare...) remplace ici "As Long" :
   On peut écrire indifféremment

   Dim i As Long

   ou

   Dim i&

   L'utilisation en est facultative, mais mon avis personnel est que c'est regrettable. Lorsqu'on utilise une variable, il vaut mieux savoir quel est son type. Outre que ce principe oblige à réfléchir sur les objets qu'on manipule (ce qui ne peut pas nuire), il permet aussi d'obtenir un code généralement moins gourmand en mémoire et plus rapide à l'exécution.
   
   Ici, i servant à indexer les lignes d'une plage, il désigne potentiellement un entier pouvant aller de 1 à 1 048 576 ; par conséquent il est naturel de déclarer i comme variable de type entier long.
   Le type entier (déclaré par Dim i As Integer ou Dim i%) est moins approprié car il ne permet pas de dépasser 32 768. Bien entendu, si vous êtes certain que dépasser cette valeur n'arrivera jamais, vous pouvez choisir ce type.
   
   
2. L'argument Cible n'est pas facultatif.
   La procédure Worksheet_Change est exécutée chaque fois que la valeur d'une cellule (ou de plusieurs) est modifiée, par vous ou par le code VisualBasic. À chaque exécution, Cible désigne la plage de cellules qui a provoqué l'exécution.
   Ce que vous pouvez faire à volonté, c'est changer son nom. Les Étasuniens nomment usuellement cet argument Target. Comme je ne pige rien à leur dialecte (dérivé, me semble-t-il, de l'Anglais, ancienne belle langue, aujourd'hui largement dégradée, mais je m'égare...) et étant francophone pur jus, je l'ai nommé Cible.
   J'aurais aussi bien pu le nommer Toto, Albert ou la_plage_qui_a_déclenché_la_procédure selon l'humeur du moment.
3. Pour ce qui est de la modification demandée, il me semble qu'il suffit de remplacer la ligne

   If Not IsEmpty(Cel.Offset(0, -4)) Then Cel.Offset(0, -4).Value = Cel.Offset(0, -5).Value * CTva(i, 2).Value

   par

   If Not IsEmpty(Cel.Offset(0, -4)) Then Cel.Offset(0, -5).Value = Cel.Offset(0, -1).Value / (1 + CTva(i, 2).Value)

   On obtient :

Private Sub Worksheet_Change(ByVal la_plage_qui_a_déclenché_la_procédure As Range)
Dim i&, Cel As Range, CTva As Range, Plg As Range, Dat As Range, tmp
    On Error GoTo E
    Set CTva = Range("P7:Q14") 'Tableau des codes de TVA
    Set Plg = Range("H7:M35") 'Plage de calcul
    Set Dat = Intersect(Plg, la_plage_qui_a_déclenché_la_procédure) 'Cellules à traiter
    If Not Dat Is Nothing Then
        For Each Cel In Dat.Cells
            tmp = Plg(Cel.Row - Plg(1).Row + 1, Plg.Columns.Count)
            For i = 1 To CTva.Rows.Count
                If tmp = CTva(i, 1).Value Then Exit For
            Next
            If Cel.Column - Plg(1).Column = 5 Then
                If i > CTva.Rows.Count Then
                    Application.EnableEvents = 0
                        Cel.Value = Empty
                    Application.EnableEvents = 1
                Else
                    If Not IsEmpty(Cel.Offset(0, -4)) Then Cel.Offset(0, -5).Value = Cel.Offset(0, -1).Value / (1 + CTva(i, 2).Value)
                End If
            Else
                Application.EnableEvents = 0
                Select Case Cel.Column - Plg(1).Column
                    Case 0
                        If IsEmpty(Cel.Value) Then
                            Cel.Offset(0, 1).Value = Empty
                            Cel.Offset(0, 4).Value = Empty
                        Else
                            Cel.Offset(0, 1).Value = Cel.Value * CTva(i, 2).Value
                            Cel.Offset(0, 4).Value = Cel.Value + Cel.Offset(0, 1).Value
                        End If
                    Case 1
                        If i > CTva.Rows.Count Then
                            Cel.Value = Empty
                        Else
                            If CTva(i, 2).Value Then
                                Cel.Offset(0, -1).Value = Cel.Value / CTva(i, 2).Value
                                Cel.Offset(0, 3).Value = Cel.Value + Cel.Offset(0, -1).Value
                            Else
                                Cel.Offset(0, 3).Value = Cel.Offset(0, -1).Value
                            End If
                        End If
                    Case 4
                        If IsEmpty(Cel.Value) Then
                            Cel.Offset(0, -4).Value = Empty
                            Cel.Offset(0, -3).Value = Empty
                        Else
                            Cel.Offset(0, -4).Value = Cel.Value / (1 + CTva(i, 2).Value)
                            Cel.Offset(0, -3).Value = Cel.Value - Cel.Offset(0, -4).Value
                        End If
                End Select
                Application.EnableEvents = 1
            End If
        Next
    End If
Exit Sub
E:
    Application.EnableEvents = 1
End Sub

Bonne soirée.



ROGER2327
#6156

Lundi 9 Tatane 139 (Saints Ecrase - Merdre, sectateurs - fête Suprême Quarte)
4 Thermidor An CCXX, 6,5635h - ivraie
2012-W29-7T15:45:09Z

 

[Amilo]

Re : Calculs des Montants TTC, HT et TVA en VBA selon son code TVA

Bonsoir Roger,

Mille mercis pour votre réponse trés détaillée,

Le code est tellement bien fait qu'il m'est trés facile de l'adapter après une insertion de colonnes avant ou après la plage de calculs.

Concernant la modification, c'est parfait comme ça.....je m'étais un peu aventuré dans cette instruction et tenté de supprimer toute l'instruction Case 0 mais en vain...

Je me rends compte que j'ai encore pas mal de chemin à parcourir pour la maitrise de VBA....

Bonne soirée et merci encore pour tout

Cordialement