Discussion :

[MaxLogan]

Bonjour,

Je souhaiterai qu'un calcul se fasse de manière automatique dans la cellule ou l’élément est saisie. je m'explique :

j'ai un tableau dont je dois faire entrée sur plusieurs lignes et colonnes des montants en TTC.
Je veux que lorsque je tape le montant ,par ex: 1 196, et que je le valide, le montant qui apparaitra au final dans ma cellule soit 1 000, c a dire du HT.

je cherche donc une formule vba du genre :
si la cellule n'est pas vide alors la saisie est divisé par 1.196

Merci

[Kimy_Ire]

Besoin de VBA ?!

Met dans la celulle destination (là où tu veux que ton calcul soit fait) ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(Z50=""; ""; Z50/1,196)

en remplacant Z50 par la cellule d'origine.

Cela ne te suffit pas ? =)

[MaxLogan]

lol

en fait mon soucis c'est que ma cellule de destination est ma cellule de saisie. elles se confondent, c'est pour sa que je veux passer par du VBA.

le tableau fait apparaitre un récapitulatif du chiffres d'affaires des différents clients sur l'année civiles.
le souci c'est que les éléments que j'ai sont de base en TTC et je veux que lorsque j'indique le montant, il apparait directement en HT.

j'avais donc penser a
cellule active diviser par /1.196 mais le résultat que j'ai ne prends pas en compte le montant que j'ai saisie, il me fait la formule par défaut avec 1 ( 1/1.196)
j'ai essaye de faire un copier/coller dans la même cellule et ensuite le coller / 1.196 mais n’étant pas une pro du vba sa ne marche pas.

[Kimy_Ire]

Perso, je n'utiliserai pas le VBA. Je décalerai par exemple toute ma colonne en TTC en mettant la formule que je t'ai donné dans la bonne colonne.

Pour autant, si tu y tiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub TTC_HT()
Dim test As Range
 
With Worksheets("Feuil4")
    Set test = .Range("A1")
    For i = 0 To .Columns(test.Column).Find("*", , , , xlByColumns, xlPrevious).Row - 1
        If IsNumeric(test.Offset(i, 0).Value) Then
            test.Offset(i, 0).Value = (test.Offset(i, 0).Value / 1.196)
        End If
    Next i
 
End With
 
End Sub

Remplace "Feuil4" par le nom de ton onglet et "A1" par la première cellule de la colonne sur laquelle sur veux appliquer ta macro.

Dis moi si cela te convient !

/!\ => Si tu appliques deux fois ta macro tu va avoir un problème ! /!\ ^^

[patricktoulon]

bonjour

je crois avoir compris ce que tu veux faire

tapez un montant et des que tu a fini de taper le montant se change en ttc ou ht c'est bien ca

et bien avec l'évènement change ca n'est pas possible
tu va devoir passer par l'évènement "onkeypress)

si c'est la touche entrer ,alors activecell=activecell/1.196

mais ca n'est pas un bon raisonnement j'utiliserais a ta place une colonne cachée

au plaisir

[Kimy_Ire]

Je procèderais de la même manière que patricktoulon ! ^^

[MaxLogan]

merci pour ces réponses.
je vais tester la formule vba et vous tiens au courant.
Oui c'est bien cela que je veux faire. le TTC apparait en HT après ma saisie.
je comprends que c'est plus simple de mettre une colonne en plus mais
mon tableau récapitule par clients et par mois sur l'année entière. soit 24 colonnes et pleins de données a masquer ...

[Kaëléphine]

Bonjour,

J'ai fait un test avec l'événement change de ma feuil et cela fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Value = Target.Value / 1.196

maintenant cette macro ne marche pas s'il y a des lettres, il faut y ajouter une condition pour qu'elle ne se déclenche que lorsque l’événement change ce produit sur une certaine plage de cellules.

Après tu n'explique pas pourquoi cela ne fonctionne pas.

moi je ferais ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 5 And Selection.Count = 1 Then
        Target.Value = Target.Value / 1.196
    End If
    Application.EnableEvents = True
End Sub

tu remplace le 5 par le numéro de colonne que tu veux et tu peux aussi augmenter le nombre de condition pour déclencher le code que sur une plage de cellule au choix

[patricktoulon]

re

au purré elle a raison sauf que tu n'a pas essayé avec un nombre decimal

mais l'action se produit a chaque touche tapée
mais attention a la virgule la ca va bugué

donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if right(target.value,1)="," then exit sub

en premier dans la fonction

[Kaëléphine]

Désolé mais je crois que je ne t'ai pas compris.

J'ai essayé avec une virgule, cela à fonctionner chez moi sans rien changer au code VBA.

Ensuite l'événement change ne se déclenche que lors de la validation de la nouvelle valeur de la cellule, en appuyant sur entrée ou en changeant de cellule.
Et j'ai pris la précaution d'arrêter les événements de la feuille le temps du calcul donc je vois pas ou cela pêche désolé

[patricktoulon]

oui tu a raison

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.EnableEvents = False

je ne l'utilise jamais

au plaisir

[MaxLogan]

okay je suis une quiche en VBA , je dois pas faire tout correctement...

c'est trop haut lvl pour moi ... si joint un tableau teste de ce que je voudrais faire

j'ai mis le code vba et j'ai tester en colonne 5 mais rien ne change au montant que j'indique. la plage concerne sont les montants CA par mois.

[Kaëléphine]

Alors tout d'abord bonjour à vous MaxLogan et Patricktoulon

Alors en voyant ta feuille et ce que tu as écris j'ai compris que tu souhaitais que la plage de cellule C43:O43 soit divisé par 1.196. Voilà donc ce qu'il en résulte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
'Lorsque la valeur d'une cellule de cette feuil change
Private Sub Worksheet_Change(ByVal Target As Range)
    'On arrête la détection des événements de Excel
    Application.EnableEvents = False
    'si la cellule dont la valeur a changé est :
        'Entre la colonne 3 et 15 comprises
        'Entre la ligne 43 et 43 comprises
    If Target.Column >= 3 And Target.Column <= 15 And Target.Row >= 43 And Target.Row <= 43 And Selection.Count = 1 Then
        'Alors la valeur de la cellule qui a changé est divisé par 1.196
        Target.Value = Target.Value / 1.196
    End If
    'On relance la détection des événements Excel
    Application.EnableEvents = True
End Sub

Alors j'ai volontairement exagéré les commentaires pour que tu comprennes bien la macro. Je n'ai pas pu enregistré le fichier au format .xlsm car la gestion des pièces jointe du forum ne les gère pas mais du coup pense bien à enregistrer sous le fichier et sélectionner .xlsm : classeur Excel (gérant les macros) .

Ensuite dans le cas ou je n'ai pas choisi la bonne plage de cellules, il te faut modifier les conditions dans le "If". J'image que tu comprends très bien que "column" fait référence à la colonne et que "row" représente la ligne .

A partir de là si je me suis trompé, change les numéros de colonne et de ligne par ceux que tu souhaite .

Dans le code que je te passe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Row >= 43 And Target.Row <= 43

Aurait tout simplement pu être remplacé par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Row = 43

J'ai choisi d'écrire cela pour que tu puisse mettre plusieurs lignes dans ta plage de cellules à modifier.

Ensuite juste une dernière petite chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Selection.Count = 1

Je n'ai pas mis cette condition pour rien . Elle permet de ne pas déclencher la macros lors de la sélection de plusieurs cellules et donc de toute les sélectionner pour "suppr" leur valeur quand tu veux reset la plage ^^ .

Pour finir je l'ai déjà dis dans un post plus haut cette macro ne gère pas les lettres ou caractères spéciaux. donc attention à ce que tu écris dans la plage de cellules.

En espérant t'avoir aidé.

[KekeStreet]

Bonjour à tous,

Ta macro est magnifique Kaëléphine, mais je suis du premier avis de patricktoulon : il est préférable de passer par un "onkeypress"

Pourquoi ? et bien avec l'évènement Worksheet_Change la valeur est modifiée à chaque changement de cellule, y compris avec des copier/coller. Au final il est très facile par erreur de voir une cellule se faire diviser une 2ème fois par inadvertance non ?

Ou alors une piste que j'aurais utilisé si j'avais choisi ta méthode :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
 
    If Selection.Count = 1 _
    And Target.Column >= 3 _
    And Target.Column <= 15 _
    And Target.Row >= 43 _
    And Target.Row <= 43 _
    And IsNumeric(Target.FormulaLocal) _
    Then
        Target.Formula = "=" & Target.Value & "/1.196"
    End If
 
    Application.EnableEvents = True
End Sub

En gros je rajoute : si la formule est numérique (donc si on entre une valeur numerique) ça change automatiquement la formule par la =valeur/1,196. Ainsi sur copier coller ou revalidation de la cellule il n'y aura pas de 2eme division.

De plus la valeur souhaité (HT) est affichée, et la valeur avec TVA est facilement visible dans la formule. Plus pratique, une pierre deux coups. Qu'en pense-tu ?

[Kaëléphine]

Oui c'est une excellente solution au problème de ma macro

Par contre ta fait une toute petite erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Formula = "=" & Target.Value & "/1.196"

Va ce traduire à la compil par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Formula = "=xxx,xxx/1.196"

et sa va planter ^^ .
Par contre en changeant par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.Formula = "=" & Target.Formula & "/1.196"

ça devrais fonctionner et donc c'est une excellente amélioration de ce que j'ai fait. Merci à toi KekeStreet

[KekeStreet]

Zut j'l'avais pas vue celle la.. comme quoi le travail en équipe c'est plutôt éfficace..

j'ai modifié au dernier moment en fait, avant de faire la comparaison avec .FormulaLocal j'utilisais .Formula en changeant les "." par le xlDecimalSeparator. ça marchait bien mais ça faisait un peu bricolage.

Et quand j'ai changé aussi l'instruction pour virer les xlDecimalSeparator j'ai merdé! l'ancienne c'était :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Target.FormulaLocal = "=" & Target.Value & "/1" & Application.International(xlDecimalSeparator) & "196"

Merci donc d'avoir apporté cette correction à ma correction
Il est bien ce code maintenant, finalement ptete même mieux que avec un "onkeypress"



Au passage : encore une très légère amélioration, je mettrais bien un petit "On Error Resume Next" pour éviter que en cas d'erreur Application.EnableEvents reste à False, ça compliquerais un peu la suite des évènements

[Kaëléphine]

Je suis bien d'accord avec toi mais disons qu'avec ton amélioration je vois mal comment avec une utilisation normal il la ferait planter.

Maintenant je suis entièrement d'accord qu'il vaut mieux le mettre .
Ne jamais sous-estimer l'utilisateur !!!

[patricktoulon]

bonjour
la solution de kaëléphine n'est pas si mauvaise au contraire

parcontre on pourait tout simplement changer une des propriété dans la cellule le font ou le bold ou voir meme la couleur quand on a calculer

et metre une condition en plus dans sa macro qui deviendrai la primaire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
if tacellule.font.bold=false then
ta cellule=tacellule/1,196
tacellule .font.bold=true
else
 
end if

ce qui reviendrai a dire que si le bold est true on calcul pas(c'est deja fait )
et le tour est joué
les propriétés il y en a quelques une a toi de choisir

c'est juste une idée comme ca
au plaisir

[MaxLogan]

Bonjour aux Experts du VBA,

Un Grand MERCI à vous tous !!!

et plus spécialement à Kaëléphine !!!

ça marche d'enfer :p

[pralinenoisette]

Bonjour,

Je vous remercie pour le code présent dans la conversation , cependant j'aurais souhaiter savoir comment affecter plusieurs colonnes à cette formule de calcule ? Je m'explique je souhaite que la formule "/1.2" soit uniquement affecter aux colonnes B1:B50, D150 etc.

Je souhaiterais également savoir s'il était possible de faire des calculs dans la cellule ? J'ai fait plusieurs essais c'est à dire que par exemple :
Si j'ai écrit dans la cellule B2 : 200€ et que je change de cellule la formule /1.2 va bien s'appliquer. Cependant si je reviens dans ma cellule B2 et que je modifie ma formule afficher =200/1.2 par =200+500, la formule /1.2 ne s'applique plus. J'imagine que c'est parce qu'elle a déjà été appliqué sur cette cellule.

Est-ce qu'il y a un moyen de faire pour que cela fonctionne ?

Je suis débutante en excel et j'ai besoin de votre aide.

Merci par avance ;-)