formule - macro Résolu

Question

Bonjour,

Dans la colonne N de ma feuille1 que j’ai nommée Produits j’ai cette formule,

=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK"))

J’aurais voulu savoir si c’était possible de mettre cette formule dans une macro ?
Et quel serait le code a mettre si tel est le cas ?

Merci de votre aide…

lermite222 · Answer

Bonjour,
Range("A1").Formula =  "=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK")) "
Remarque tu met NB.Vide pour une seule cellule, tu peu remplacer par VIDE(I2) ou I2<>""
A+

tchulio · Answer

Bonjour lermite222,

Merci pour cette réponse rapide,

j'avais mal formulé ma question, ce que je voudrais, c'est que la formule ne s'applique pas juste à la ligne 2 mais à tout le tableau, et que lorsque j'insère mes données dans ma feuille, ça se calcul automatiquement...

lermite222 · Answer

Je ne comprend pas bien, en mettant NB.VIDE = ? ça peu changer donc ce serra presque toujours NON.
Explique un peu plus.
A+

tchulio · Answer

Non en fait ta réponse est juste mais ne s’applique pas a toute la colonne N mais juste a la ligne 2.

Ce que je voudrais c’est que ça s’applique a toute la colonne N

lermite222 · Answer

Cette fois, si j'ai compris, tu veux vérifier si toute la colonne est vide sauf la ligne 2
dans ce cas, faut mettre une plage de cellule
"=SI(NB.VIDE(I2:i100)=1
par exemple
Mais tu ne serra jamais certain que c'est la ligne 2 qui n'est pas vide !
dans ce cas...
=SI(ET(I2;NB.VIDE(I3:i100)=0);

tchulio · Answer

Non, désolé, je ne parlais pas de la fonction NB.VIDE mais de la macro,

Range("A1").Formula = "=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK"))

Cette ligne fonctionne mais je voudrais qu’elle s’applique a toute ma feuille ou, par exemple, de 

=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK"))   jusqu’à 
=SI(NB.VIDE(I15000)=1;"";SI(I15000/G15000<0.97;"NCR";"OK"))   

et que lorsque je rentre des données dans ma feuille ça se calcule automatiquement avec la macro, un peu comme ce que vous m’aviez fait la dernière fois, lorsque je rentre une date, l’année, la semaine, le temps de passage…. Tout se calculait automatiquement…

lermite222 · Answer

Range("J2").Copy 'à adapter à la colonne où se trouve la formule
    
    Range("J3:J15000").Select 'à adapter à la colonne où se trouve la formule
    ActiveSheet.Paste
Tu dit...
Mais comme je dit plus haut, SI(I2:    suffit
Autre remarque, si G est vide fait une erreur DIV/0
faudrait mettre
SI(ET(I2;G2); ...
A+

tchulio · Answer

Je 'arrive pas a faire comme tu dis, si des fois, voici le fichier (simplifié) en question.
Merci encore...

http://www.cijoint.fr/cjlink.php?file=cj200811/cij35BaCg6.xls

lermite222 · Answer

OK, faut inverser la 1ère condition...
=SI(ET(G2;I2);SI(I2/G2<0,97;"NCR";"Ok");"")
Et oublie pas le End Sub dans ta sub Calc.

tchulio · Answer

Voilà ce que ça donne alors.

mais la ligne de formule se met en rouge  ==> Range("A1").Formula = "=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK"))




Private Sub calc()


Range("A1").Formula = "=SI(NB.VIDE(I2)=1;"";SI(I2/G2<0.97;"NCR";"OK"))

Range("N2").Copy 'à adapter à la colonne où se trouve la formule

Range("N3:N15000").Select 'à adapter à la colonne où se trouve la formule
ActiveSheet.Paste



End Sub

tchulio · Answer

d'accord merci de ton aide, ça ça fonctionne, mais effectivement, ce que moi je voudrais, c'est que le calcul se fasse dans la macro et non pas dans ma feuille..

Merci quand même pour ta précieuse aide.

tchulio · Answer

que le calcul se fasse dans la macro alors; si c'est plus simple et pas trop difficile a paramétrer en cas de modification..

lermite222 · Answer

IMPOSSIBLE de faire quoi que ce soit sur ta feuille, toutes les cellules sont verrouillées  mais toujours est-il que ni une fonction personnalisée; ni une fonction générale ne fonctionne, pas de possibilité d'accéder à la feuille produits par code.
Pas possible de mettre la fonction personnalisée par code...
Je n'ai jamais rencontré ça, peu être Eric ?? ou un autre intervenant ?
En déverrouillant toutes les cellules, cette macro fonctionne mais c'est pas pratique, il faut la rappeler chaque fois que des lignes sont ajoutées où modifiées
j'ai aussi tenter FormulaR1C1, mais idem.
Sub Resultat()
Dim Lig As Long
Dim Col As Integer
    Sheets("Produits").Select
    For Lig = 2 To Range("C65536").End(xlUp).Row
    If Cells(Lig, 7) And Cells(Lig, 9) Then
        If (Cells(Lig, 9) / Cells(Lig, 7)) < 0.97 Then
            Cells(Lig, 14) = "NCR"
        Else
            Cells(Lig, 14) = "OK"
        End If
    Else
        Cells(Lig, 14) = ""
    End If
    Next Lig
End Sub

Sais pas ce qui se passe sur ce classeur. Y a un truc...
A+

tchulio · Answer

d'accord ben merci pour la macro... je vais essaye de comprendre ce qui arrive sur ce classeur.... bonne soirée

tchulio · Answer

je viens d'essayer la macro, donc c'est bon ça fonctionne, par contre si la valeur en I est trop faible par rapport à G, il n'y a rien qui s'affiche, normalement il devrait s'afficher "NCR" en colonne N.

Prenons par exemple 100 en colonne G et que j'en ai que 10 en colonne I, il n'y a rien qui s'affiche, par contre si j'en ai 20 en I, il y a NCR...

Je ne vois pas ce que ça peut être....
MErci

tchulio · Answer

ok ben là ça marche mais si la valeur en I est égale à "0" ce qui arrive souvent, là il n'y a toujours rien qui s'affiche...

tchulio · Answer

En fait c'est réglé je n'avais qu'à mettre >=. Je vais faire quelques test, merci beaucoup l'aide accordée :)

tchulio · Answer

Bon j'ai regardé dans mon classeur, ça fonctionne parfaitement (pour 6000 lignes) donc c'est cool !

Pour contre je n'avais pas pensé. Ce code est bien pour calculer une feuille, mais en fait pour insérer mes données j'utilise un USF avec ce code, est ce qu'il y aurait possibilité de faire pour que ça calcule juste la ligne que j'insère et pas toute la feuille :

Private Sub NEW_VALID()

         
'Pose la question lorsque l'on clique sur Valider'
If TextBox11 <> 0 Then
        Dim Msg, Style, Ctxt, Response
        Msg = "Voulez vous vraiment enregistrer ce contrôle?"
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Response = MsgBox(Msg, Style)
        If Response = vbYes Then

Dim ligne As Integer
Dim rw As Long
'je retrouve la ligne concernée par les modifications en bouclant de 1 à 65000
For rw = 1 To 10000
If Worksheets("produits").Range("A" & rw) = Val(UserForm8.ComboBox1) Then
ligne = rw
Exit For
End If
Next

UserForm4.Show

    Application.Worksheets("produits").Cells(ligne, 9) = TextBox1.Value
    Application.Worksheets("produits").Cells(ligne, 12) = TextBox_visa.Value
    Application.Worksheets("produits").Cells(ligne, 10) = CheckBox1.Value
    Application.Worksheets("produits").Cells(ligne, 13) = Textbox2.Value
    Application.Worksheets("produits").Cells(ligne, 21) = TextBox3.Value
    Application.Worksheets("produits").Cells(ligne, 19) = TextBox5.Value
    Application.Worksheets("produits").Cells(ligne, 20) = TextBox7.Value
    Application.Worksheets("produits").Cells(ligne, 18) = TextBox9.Value
    Application.Worksheets("produits").Cells(ligne, 17) = TextBox10.Value
    Application.Worksheets("produits").Cells(ligne, 11) = CDate(TextBox11.Value)
    Application.Worksheets("produits").Cells(ligne, 22) = ComboBox2
    Application.Worksheets("produits").Cells(ligne, 24) = Format(Now, "DD/MM/YY HH:MM:SS")
    Application.Worksheets("produits").Cells(ligne, 23) = Application.UserName
    Application.Worksheets("produits").Cells(ligne, 25) = TextBox12.Value
    Application.Worksheets("produits").Cells(ligne, 8) = ""
                   
Unload Me
Range("AN1:AT1").Calculate
UserForm1.Show

End If
End If
End Sub

lermite222 · Answer

Bonjour, merci eric d'avoir pris la relève, j'ai été absent hier.
Mais ça m'énervait de pas trouver la solution et je crois que la solution suivante est beaucoup plus pratique.
CAR J'AI ENFIN TROUVE LE NOEUD DU PROBLEME.
Tes réf articles commence par des réf à des cellules et Excel les concidères comme tel et en déduit des réf circulaires, il faut mettre toute la colonne au format TEXTE et ça roule...oufffff ont peu enfin revenir à un exemple plus concret
Une fonction personnalisée est plus complète.
Dans un module général tu copie...
Function RetNCR_OK()
Dim Lig As Long
    Application.Volatile
    Lig = Application.Caller.Row
    If Cells(Lig, 7) > 0 Then
        If Cells(Lig, 9) = "" Or Cells(Lig, 9) = 0 Then
            RetNCR_OK = "NCR"
        ElseIf (Cells(Lig, 9) / Cells(Lig, 7)) < 0.97 Then
            RetNCR_OK = "NCR"
        Else
            RetNCR_OK = "OK"
        End If
    Else
        RetNCR_OK = ""
    End If
End Function
Tu peu aussi mettre ce code, maintenant ça fonctionne...
Private Sub calc()
    Range("N2").Formula = "=RetNCR_OK()"
    Range("N2").Copy 'à adapter à la colonne où se trouve la formule
    Range("N2:N15").Select 'à adapter à la colonne où se trouve la formule
    ActiveSheet.Paste
End Sub
Il n'est plus besoin de modifier la formule en cas de modification des données, mais tu doit soit recalculer (F9) ou mettre le calcul en automatique.
et tu peu simplifier la fin de ta macro par...
    With Sheets("produits")
        .Cells(ligne, 9) = TextBox1.Value
        .Cells(ligne, 12) = TextBox_visa.Value
        .Cells(ligne, 10) = CheckBox1.Value
        .Cells(ligne, 13) = Textbox2.Value
        .Cells(ligne, 21) = TextBox3.Value
        .Cells(ligne, 19) = TextBox5.Value
        .Cells(ligne, 20) = TextBox7.Value
        .Cells(ligne, 18) = TextBox9.Value
        .Cells(ligne, 17) = TextBox10.Value
        .Cells(ligne, 11) = CDate(TextBox11.Value)
        .Cells(ligne, 22) = ComboBox2
        .Cells(ligne, 24) = Format(Now, "DD/MM/YY HH:MM:SS")
        .Cells(ligne, 23) = Application.UserName
        .Cells(ligne, 25) = TextBox12.Value
        .Cells(ligne, 8) = ""
    End With
A+

tchulio · Answer

Donc j'ai regardé, ça fonctionne.

Le problème avec cette solution, c'est qu'il y a des formules. Car moi dans mon tableau, j'ai environ 7000 lignes, et des stats avec des TCD, et des fonctions sommeprod.
Tu va donc comprendre que moins j'ai de formules, mieux ça tourne.

En fait avec la macro que tu m'avais faites ça marchait parfaitement pour le calcul de la feuille,

Sub Resultat()
Dim Lig As Long
Dim Col As Integer
    Sheets("Produits").Select
    For Lig = 2 To Range("C65536").End(xlUp).Row
   If Cells(Lig, 7).Value > 0 And Cells(Lig, 9).Value > 0 Then 
        If (Cells(Lig, 9) / Cells(Lig, 7)) < 0.97 Then
            Cells(Lig, 14) = "NCR"
        Else
            Cells(Lig, 14) = "OK"
        End If
    Else
        Cells(Lig, 14) = ""
    End If
    Next Lig
End Sub

Ce qu'il m'aurait juste fallu en suplément c'est que lorsque je clique sur mon bouton Valider, a la fin des instructions pour rentrer mes valeurs:

 With Sheets("produits")
        .Cells(ligne, 9) = TextBox1.Value
        .Cells(ligne, 12) = TextBox_visa.Value
        .Cells(ligne, 10) = CheckBox1.Value
        .Cells(ligne, 13) = Textbox2.Value
        .Cells(ligne, 21) = TextBox3.Value
        .Cells(ligne, 19) = TextBox5.Value
        .Cells(ligne, 20) = TextBox7.Value
        .Cells(ligne, 18) = TextBox9.Value
        .Cells(ligne, 17) = TextBox10.Value
        .Cells(ligne, 11) = CDate(TextBox11.Value)
        .Cells(ligne, 22) = ComboBox2
        .Cells(ligne, 24) = Format(Now, "DD/MM/YY HH:MM:SS")
        .Cells(ligne, 23) = Application.UserName
        .Cells(ligne, 25) = TextBox12.Value
        .Cells(ligne, 8) = ""
    End With

Que j'ait un code pour que ce calcul se fasse juste sur la ligne que j'insère... Avec un Row.source ou je sais pas si c'est possible,,,,

MErci encore

lermite222 · Answer

Pas de problème
Tu ajoute cette petite sub
Sub ResultatPartiel(lig As Long)
    If Cells(lig, 7) And Cells(lig, 9) Then
        If (Cells(lig, 9) / Cells(lig, 7)) < 0.97 Then
            Cells(lig, 14) = "NCR"
        Else
            Cells(lig, 14) = "OK"
        End If
    Else
        Cells(lig, 14) = ""
    End If
End Sub

et après End With tu met
    ResultatPartiel Ligne
A+

tchulio · Answer

je l'ajoute dans un module ou dans mon userform?

lermite222 · Answer

Tu peu mettre en dessous de l'autre macro, mais faut ajouter une ligne
    Sheets("Produits").select
juste au début.
A+

lermite222 · Answer

ou bien...
With Sheets("produits") 
    .Cells(ligne, 9) = TextBox1.Value
    .Cells(ligne, 12) = TextBox_visa.Value
    .Cells(ligne, 10) = CheckBox1.Value
    .Cells(ligne, 13) = Textbox2.Value
    .Cells(ligne, 21) = TextBox3.Value
    .Cells(ligne, 19) = TextBox5.Value
    .Cells(ligne, 20) = TextBox7.Value
    .Cells(ligne, 18) = TextBox9.Value
    .Cells(ligne, 17) = TextBox10.Value
    .Cells(ligne, 11) = CDate(TextBox11.Value)
    .Cells(ligne, 22) = ComboBox2
    .Cells(ligne, 24) = Format(Now, "DD/MM/YY HH:MM:SS")
    .Cells(ligne, 23) = Application.UserName
    .Cells(ligne, 25) = TextBox12.Value
    .Cells(ligne, 8) = ""

   If .Cells(ligne, 7) And .Cells(ligne, 9) Then
        If (.Cells(ligne, 9) / .Cells(ligne, 7)) < 0.97 Then
            .Cells(ligne, 14) = "NCR"
        Else
            .Cells(ligne, 14) = "OK"
        End If
    Else
        .Cells(ligne, 14) = ""
    End If

End With 
A+

tchulio · Answer

ok j'ai compris pardon.

Mais par contre ça ne fonctionne pas. Quand je clique sur valider, mes ligne s'insère, mais le calcul ne se fait pas...

tchulio · Answer

ok je regarde ça cet aprem merci

tchulio · Answer

BOnjour, désolé de répondre seulement maintenant, beaucoup d'urgence au travail et pas le temps ce w.e...

Donc j'ai essayé, ça marche quand je rentre mes données avec le USF, par contre même problème qu'auparavant, si la quantité ok est "0" , il n'y a rien qui s'affiche.

et si je mets cette ligne, 
If Cells(lig, 7).Value >= "0" And Cells(lig, 9).Value >= "0" Then
ça bug, ça ne marche pas avec le USF,

Merci

lermite222 · Answer

Apparement tu oublie les points...
If .Cells(lig, 7).Value >= "0" And .Cells(lig, 9).Value >= "0" Then 
A+

tchulio · Answer

Ok donc ce code fonctionne maintenant parfaitement lorsque je fait les saisie avec le USF, par contre quand je fait un calcul total de la feuille avec ce code, si j’ai une valeur dams ma colonne 7 et pas de valeur dans la 9, ça m’affiche « NCR » dans la colonne 14. Normalement il n’y a rien qui doit s’afficher.

A mon avis le pb vient de cette ligne 

If Cells(Lig, 7).Value >= 0 And Cells(Lig, 9).Value >= 0 Then

J’ai mis un >=0 car si ma quantité ok est “0”, il faut que ça affiche NCR.

Sub Resultat()
Dim Lig As Long
Dim Col As Long
    Sheets("Produits").Select
    For Lig = 2 To Range("C65536").End(xlUp).Row
    If Cells(Lig, 7).Value >= 0 And Cells(Lig, 9).Value >= 0 Then
        If (Cells(Lig, 9) / Cells(Lig, 7)) < 0.99 Then
            Cells(Lig, 14) = "NCR"
        Else
            Cells(Lig, 14) = "OK"
        End If
    Else
        Cells(Lig, 14) = ""
    End If
    Next Lig
End Sub

merci

lermite222 · Answer

Ca devrait aller avec...
Sub Resultat()
Dim Lig As Long
Dim Col As Long
    Sheets("Produits").Select
    For Lig = 2 To Range("G65536").End(xlUp).Row
        If Cells(Lig, 7) > 0 Then
            If Cells(Lig, 9) = "" Or Cells(Lig, 9) = 0 Then
                Cells(Lig, 14) = "NCR"
            ElseIf (Cells(Lig, 9) / Cells(Lig, 7)) < 0.97 Then
                Cells(Lig, 14) = "NCR"
            Else
                Cells(Lig, 14) = "OK"
            End If
        Else
            Cells(Lig, 14) = ""
        End If
    Next Lig
End Sub
A+

tchulio · Answer

Avec ce code aussi , lorsqu'il n'y a pas de quantité OK en "9", ça m'inscrit "NCR".

En fait ce qu'il faut c'est que quand "9" = 0 il y ait NCR
et que quand "9" est vide, il n'y ait rien qui ne s'affiche

lermite222 · Answer

Tu dit.....
Donc j'ai essayé, ça marche quand je rentre mes données avec le USF, par contre même problème qu'auparavant, si la quantité ok est "0" , il n'y a rien qui s'affiche. 

Avec ce code aussi , lorsqu'il n'y a pas de quantité OK en "9", ça m'inscrit "NCR". 

Décide une bonne fois pour toute Grrrrr

tchulio · Answer

Non, quand il y a "0" en quantité ok, il y a NCR. ça on est d'accord.

Par contre s'il il n'y a pas de quantité ok, s'il il n'y a rien d'écrit, si la case est vide quoi, là il ne devrait rien y avoir.

tu comprends mieux ce que je veux dire?

lermite222 · Answer

Je n'ai pas réalisé plutôt, mais quand tu modifie la ligne ...
If Cells(lig, 7).Value >= "0" And Cells(lig, 9).Value >= "0" Then 
Réfléchi un tout.. tout...tout petit peu...
La condition où il n'y à rien dans aucune des cellules ne se produira jamais car tu met (c'est toi qui modifie) 
7=>0 et 9=>0 CE SERRA TOUJOURS VRAI, cogite la-dessus et tu trouverras une solution. Si pas ont en rediscuterras.
A+
PS: Il faut aussi que tu réfléchisse un peu et jusque maintenant je n'ai fait que te PONDRE la solution ce qui n'était pas te rendre service.

tchulio · Answer

Je suis vraiment nul...

Des on cherche compliqué quand c'est tout simple....

Il y avait juste à mettre les "" autour des "0", maintenant ça fonctionne parfaitement...

Encore merci à toi pour toute ton aide!

Formule - macro

35 réponses

Votre réponse

Discussions similaires