Cherche un Code VBA avec condition

Bonjour,
Je suis nouveau sur le site et ne suis pas certain d'être au bon endroit pour poser ma question mais je tente quand même.
Je cherche une formule qui me permettrait, dans un tableau xls, de faire une soustraction sur base d'une condition.
Par exemple, dans un petit tableau avec 2 colonnes (A et B) et 5 lignes de 1 à 5.
La celulle A5 est alimentée par des données venant d'un formulaire.
Pour calculer la valeur de la celulle B5, ma formule est la suivante: si A5 <> 0 alors B5 = A5 - A4 sinon B5 = A5 - (la valeur de la première cellule différente de 0 en remontant de A5 vers A1)

Cela ne doit pas être très compliqué mais je ne suis pas un expert en vba et je cherche une solution depuis plusieurs jours sans trouver un code adapté.
Merci pour votre aide,
Bien à vous
Philippe

Bonjour Philippe,

Je pense qu'avec un fichier et un exemple de ce que vous voulez, il serait plus facile de comprendre 🤔

A+

Citation:

---

Envoyé par BrunoM45

Bonjour Philippe,

Je pense qu'avec un fichier et un exemple de ce que vous voulez, il serait plus facile de comprendre 🤔

A+

---

En effet, voici un petit fichier avec qqs explications complémentaires.
J'espère que ça peut aider à voir ce que je cherche.
Merci

Bonjour Philippe

Je ne sais pas si les valeurs sont systématiquement grandissante dans ton tableau,
si tel est le cas, voici une formule à mettre en B2, recopie vers le bas

Code:

---

=SI(ET(ESTNUM(A1);A2<>0);A2-MAX(A$1:A1);0)

---

Sinon, je ne sait pas si c'est faisable en Power Query, il faudra alors une fonction personnalisée en VBA

A+

Hello,
Voici une fonction personnalisée en VBA avec comme paramètre une cellule.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Function CalcDiff(Cellule As Range) Dim x As Integer CalcDiff = 0 If Cellule.Value = 0 Then Exit Function x = -1 Do If Not IsNumeric(Cellule.Offset(x, 0)) Then Exit Do If Cellule.Offset(x, 0).Value <> 0 Then CalcDiff = Cellule.Value - Cellule.Offset(x, 0) Exit Do End If x = x - 1 Loop End Function

---

Le principe :
Si la cellule est égale à 0 on renvoie 0
Si la cellule est différente de 0 on boucle sur les cellules au-dessus jusqu'à ce que l'on trouve une cellule dont la valeur est différente de 0 ou n'est pas numérique. Au pire on sortira de la boucle quand on atteindra la ligne de titre du tableau car les valeurs ne sont pas numériques. La valeur renvoyée est la différence entre la valeur de la cellule de départ et la première valeur non nulle trouvée dans les cellules au-dessus.


A voir si cela fonctionne dans tous les cas de figure.


En tout cas cela semble fonctionner avec le tableau fourni :

Pièce jointe 627429

Ami calmant, J.P

Grand merci BrunoM45,
en effet la formule fonctionne parfaitement si les valeurs de la colonne A sont toujours croissantes.
Malheureusement, dans mon tableau qui contient une quinzaine de colonnes et qqs centaines de lignes, il y a 2 colonnes dans lesquelles il peut y avoir de temps en temps une valeur décroissante et dans ce cas le résultat n'est pas correct.
S'il y avait un moyen d'adapté la formule ce serait magnifique.
Bonne journée à toi et à bientôt surement,
Philippe

Bonjour Philippe

Il faudra utiliser la super fonction de Jurassik pork que je salue ;)

A+

Bonjour Jurassic Pork,
Un tout grand merci pour cette fonction magique qui fonctionne parfaitement et que j'ai pu appliquer à toutes les colonnes calculées de mon tableau.
En fait vous avez traduit en code ce que j'avais dans ma petite tête de pensionné apprenti en VBA, c'est super.
Je tiens également à vous remercier BrunoM45 et vous pour votre rapidité à me répondre, c'est encourageant.
Si je peux me permettre il y 2 choses que je voudrais encore demander:
1: Je me suis trompé concernant le résultat à obtenir pour la première ligne du tableau en cellule B2. En fait, si la cellule en (-1,0) est numérique cela signifie que je suis sur la première ligne contenant des valeurs et dans ce cas uniquement la valeur de B2 devrait être égale à la valeur de A2.
2: Dans le cas de changement de valeur dans une des cellules existante de la colonne A, il faudrait que toutes les valeurs de la colonne B soient recalculées afin de garder un résultat correct.
Avec la fonction actuelle, seule la cellule de la ligne sur laquelle je change la valeur de A est recalculée et ensuite les autres restent identiques.
Est-il envisageable de recalculer l'ensemble des cellules en colonne B ?

J'espère que mes explications sont claires.
Merci encore,
Bien à vous,
Philippe

Tu as raison, BrunoM45 je vais utiliser la fonction de Jurassic Pork.
Merci quand même pour ta formule et ta rapidité de réponse.
Bien à toi,
Philippe

Citation:

---

Envoyé par Philippe Boulanger

2: Dans le cas de changement de valeur dans une des cellules existante de la colonne A, il faudrait que toutes les valeurs de la colonne B soient recalculées afin de garder un résultat correct.
Avec la fonction actuelle, seule la cellule de la ligne sur laquelle je change la valeur de A est recalculée et ensuite les autres restent identiques.
Est-il envisageable de recalculer l'ensemble des cellules en colonne B ?

---

L'astuce consiste à ajouter un paramètre pipotesque à la fonction personnalisée qui correspond à la colonne A. Ce paramètre ne sera pas utilisé dans la fonction, mais comme il y a référence à toutes les cellules de la colonne A, dès qu'une cellule de la colonne A change, les formules sont recalculées.

Code:

---

Function CalcDiff(Cellule As Range, Optional Colonne As Range)

---

A mettre comme formule :

Code:

---

=CalcDiff([@[Données venant du formulaire]];Table1[[#Tout];[Données venant du formulaire]])

---

Ami calmant, J.P

Super JP, la nouvelle formule fonctionne parfaitement après le petit changement dans le code.
Pour la première partie de ma demande concernant la première ligne, tout n'est pas clair dans mon descriptif, je vais essayer de reformuler ça au mieux.
Merci pour la réactivité.
Philippe

Hello,

Citation:

---

Envoyé par Philippe Boulanger

1: Je me suis trompé concernant le résultat à obtenir pour la première ligne du tableau en cellule B2. En fait, si la cellule en (-1,0) est numérique cela signifie que je suis sur la première ligne contenant des valeurs et dans ce cas uniquement la valeur de B2 devrait être égale à la valeur de A2.

---

Sur la première ligne de données du tableau, il suffit de mettre comme formule :

Code:

---

=[@[Données venant du formulaire]]

---

Ami calmant, J.P

Bonjour JP,
Oui je pourrais faire ça mais si je change la formule d'une cellule, cette nouvelle formule se recopie sur toutes les cellules de la colonne.
Aussi j'aimerais avoir la même formule dans toutes les cellules de ma colonne.
Ne serait-il pas possible d'ajouter une condition dans la fonction actuelle pour dire:
La valeur de la cellule B2 = la valeur de la cellule A2 si A2 est pour la première cellule <> 0 sinon on applique la fonction actuelle.
ex: B2 = A2 pcq c'est la première ligne de ma table et que je ne peux pas calculer puisque je n'ai pas de valeur en A1 (Headers).
B3 = A3 si A2 = 0 sinon fonction actuelle.
B4 = A4 si A3 et A2 = 0 sinon fonction actuelle
B5 = A5 si A4, A3 et A2 = 0 sinon fonction actuelle.

Extrait de mon tableau: (en espérant que vous le recevrez)
C2 = B2
E2 = D2
H4 = G4
J4 = I4

Je pense qu'après cela tout devrait fonctionner comme souhaité.
Merci JP pour le support
Bien à vous,
Philippe

Hello,
pour voir comment ne pas recopier automatiquement une formule dans une colonne de tableau structuré regarde cette vidéo :

Mieux comprendre la recopie des formules dans un tableau structuré

https://www.youtube.com/watch?v=-9JK9oVkB6E


Sinon :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Function CalcDiff(Cellule As Range, Optional Colonne As Range) Dim x As Integer CalcDiff = 0 If Cellule.Value = 0 Then Exit Function If Cellule.ListObject.DataBodyRange.Row = Cellule.Row Then CalcDiff = Cellule.Value Exit Function End If x = -1 Do If Not IsNumeric(Cellule.Offset(x, 0)) Then Exit Do If Cellule.Offset(x, 0).Value <> 0 Then CalcDiff = Cellule.Value - Cellule.Offset(x, 0) Exit Do End If x = x - 1 Loop End Function

---

Cellule.ListObject.DataBodyRange.Row est la première ligne de données du tableau dans lequel se trouve la cellule cellule

Ami calmant, J.P

Ok merci JP,
je vais regarder cette vidéo qui m'en apprendra encore une peu plus.
Je vais aussi essayer d'adapter la fonction mais ça c'est une autre paire de manche pour moi.
Bonne journée,
Philippe

J'ai modifié la fonction et elle marche très bien pour autant que la valeur dans les cellules de la ligne 1 ne soit pas égale à 0.
J'ai donc changé la valeur des cellules de la ligne 1 et j'ai mis 0,00001, dans ce cas cela n'influence pas vraiment les calculs et les valeurs calculées sont toujours exactes.
La solution me semble adaptée à mes besoins et les résultats sont exacts.
Grand merci encore pour la rapidité de réaction et la qualité des propositions.
Je termine mes tests demain et si tout est ok je propose de clôturer la discussion.
Bonne soirée JP et à bientôt peut être pour d'autres questions.
Bien à toi,
Philippe

Bonjour à tous,
j'ai terminé les tests de la solution proposée par Jurassic Pork et suis très content du résultat obtenu.
Un grand merci pour l'aide apportée et l'efficacité des interventions.
Mon problème étant résolu je propose de clôturer la discussion.
Merci également à BrunoM45 qui avait fait une autre proposition.
Bien à vous,
Philippe