Fonction NB.SI VBA

Version imprimable

13/05/2016, 11h47
mateoa44

Fonction NB.SI VBA
Bonjour à tous !

Petit soucis avec la fonction NB.SI
Code:

1 2 3 4 5 With Sheets("Synthese") .Range("E4").Select Selection.NumberFormat = "0"" PDV en progression""" ActiveCell.Formula = "=NB.SI(PLAGE;">2")"
J'automatise la création de tableaux de synthèses, la plage n'est pas la même pour chaque fichier, ce que je voudrais en fait, c'est que le comptage commence à la ligne H3 (bon ça ok j'y arrive sans problème) MAIS que ça se termine à la dernière ligne renseignée ; c'est possible de "dire" ça à la fonction NB.SI ?

Merci par avance pour l'aide que vous pourrez m'apporter !!!

Bonjour,
Tu peux utiliser ce code.

Code:

1
2
x = Range("H"& Rows.count).End(x1Up).Row
Application.WorksheetFunction.CountIf(Range("H3:H" & x), toncritère)

13/05/2016, 12h12
Philippe Tulliez

Bonjour,
Cette ligne ActiveCell.Formula = "=NB.SI(PLAGE;">2")" renverra une erreur car la propriété Formula attend une chaîne de caractères contenant une formule en anglais soit COUNTIF
Pour en savoir plus, tu peux lire Ecrire une formule dans Excel à l'aide d'une procédure VBA
13/05/2016, 12h19
Kiouane

Philippe est ce que mon code pourrait lui convenir?
13/05/2016, 13h41
mateoa44

1 pièce(s) jointe(s)

Bonjour Kiouane, bonjour Philippe,

Tout d'abord, je vous remercie à tous les deux pour vos réponses ! J'ai pris connaissance du lien que tu as envoyé Philippe, du coup tu m'apprends quelque chose ; utiliser le nom en Anglais, merci :)

Kiouane, je vais essayer ton code et je te tiens au courant du résultat, merci pour le partage !! :)

EDIT : j'ai oublié de préciser que le résultat, je souhaite le placer dans une autre feuille intitulée "synthese", par contre le calcule se fait sur la feuille "telephonie"

J'ai essayé sur un autre fichier excel pour essayer :

Pièce jointe 209606

Bonjour mateoa44,

essaye ça pour voir :

Code:

1
2
3
4
5
6
7
8
Sub fonction()
 
With Sheets(2)
    .Range("E4").NumberFormat = "0"" PDV en progression"""
    .Range("E4") = Application.WorksheetFunction.CountIf(Sheets(1).Range("A1:A" & Sheets(1).Range("A1").End(xlDown).Row), ">2")
End With
 
End Sub

;)

13/05/2016, 14h53
Kiouane

Citation:

Envoyé par mateoa44

Bonjour Kiouane, bonjour Philippe,

Tout d'abord, je vous remercie à tous les deux pour vos réponses ! J'ai pris connaissance du lien que tu as envoyé Philippe, du coup tu m'apprends quelque chose ; utiliser le nom en Anglais, merci :)

Kiouane, je vais essayer ton code et je te tiens au courant du résultat, merci pour le partage !! :)

EDIT : j'ai oublié de préciser que le résultat, je souhaite le placer dans une autre feuille intitulée "synthese", par contre le calcule se fait sur la feuille "telephonie"

J'ai essayé sur un autre fichier excel pour essayer :

Pièce jointe 209606

Tu dois affecter ton application.function à une cellule par exemple. C'est pour cela que tu reçois cette erreur.

Edit : Pour la syntaxe de ta plage, ça devrait plutôt être sous forme d'un Range.
13/05/2016, 15h06
mateoa44

Bonjour Al__22, merci pour ta réponse !!! Sur mon fichier test ça marche parfaitement, du coup, baaah, merci beaucoup haha :)

@Kilouane :

Code:

Application.WorksheetFunction.CountIf(Range("H3:H" & x), ">2")

Je ne sais pas comment ajouter la feuille dans ce code, j'ai essayé plusieurs choses mais qui provoquent des erreurs à chaque fois !
13/05/2016, 15h14
DeathZarakai

Sinon si tu veux rester sur ta formule de base, remplace le Formula par FormulaLocal.

Comme le stipule le lien donné par Philippe, .formula attendra TOUJOURS une formule Excel en ANGLAIS.
Alors que FormulaLocal dépend de ton installation, si ton installation est en français ce qui semble être vu que tu utilise NB.SI alors il faut utilisé la méthode FormulaLocal et non Formula.

DeathZarakai

EDIT : range() -> Worksheets("nom de ta feuille").Range()

Pour te dépanner mais il y aura sûrement mieux à faire.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub fonction()
 
 
Sheets(1).Activate
    x = Range("C" & Rows.Count).End(xlUp).Row
    Cells(1, 1).Value = Application.WorksheetFunction.CountIf(Range("H3:H" & x), ">2")
 
Sheets(2).Activate
    Range("E5").NumberFormat = "0"" PDV en progression"""
    Cells(5, 5).Value = Sheets(1).Cells(1, 1).Value
 
 
End Sub

Je vous remercie à tous pour vos réponses !

J'ai gardé la solution de Al__22 qui fonctionne !
Code:

1 2 .Range("E4").NumberFormat = "0"" PDV en progression""" .Range("E4") = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("I4:I" & Sheets("Telephonie").Range("I4").End(xlDown).Row), ">2")
EDIT : il est vrai que j'aurais pu, mais comme je suis en stage, je ne sais pas qui va utiliser le code après, donc autant laisser en anglais !! (au cas où ^^)

Heureux si ça t'a aidé :mrgreen:
Après si tu le souhaites plutôt que de mettre le n° d'index des feuilles tu peux mettre leur noms :
sheets(1) = sheets("Téléphonie") etc
le 1 signifie que ta feuille est en position 1 par rapport aux autres donc si jamais tu la bouge de place cela foire tes calculs donc à toi de voir ce que tu préfères ;)

Et pour plus de flexibilité je te le remets avec les noms des feuilles et des explications :
Code:

1 2 3 4 5 6 7 8 9 10 Sub fonction() With Sheets("Synthèse") 'Tout ce qui suit avec un "." au début appartient à la feuille Synthèse .Range("E4").NumberFormat = "0"" PDV en progression""" 'ta mise en format perso .Range("E4") = Application.WorksheetFunction.CountIf(Sheets("Téléphonie").Range("A1:A" & Sheets("Téléphonie").Range("A1").End(xlDown).Row), ">2") 'tu dis à VBA de mettre dans la cellule E4 le résultat de ton countif 'Sheets("Téléphonie").Range("A1").End(xlDown).Row : renvoie la dernière ligne avec une valeur dans la colonne A End With End Sub
Bon courage pour la suite

13/05/2016, 16h53
mateoa44

Je te remercie pour le partage et les explications ! ^^
J'avais déjà modifié (1) par "Telephonie" :D

J'aurais tout de même une dernière question sur le count if !!

Je fais face à un nouveau problème !!

J'aimerais que .Range("E12") prenne la valeur de cette formule.. mais avec ta technique, je m'emmêle les pinceaux !

Code:

=(NB.SI(P5:P36;"=100%")/NB.SI(O5:O36;">0"))

C'est le même principe que :

Code:

.Range("E8") = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("L4:L" & Sheets("Telephonie").Range("L4").End(xlDown).Row), ">2")

sauf que cette fois il y a une division par un autre countif haha

Tu as une idée de comment faire ? ^^'

Bonjour mateoa,

Pour plus de lisibilité tu peux mettre en variable string tes deux valeurs puis dire de mettre le résultat dans la cellule de ton choix, par exemple :
Code:

1 2 3 cif1 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") cif2 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1/cif2
A toi d'adapter après avec les valeurs dont tu as besoins ;)

Citation:
Envoyé par Al__22
Bonjour mateoa,

Pour plus de lisibilité tu peux mettre en variable string tes deux valeurs puis dire de mettre le résultat dans la cellule de ton choix, par exemple :
Code:

1 2 3 cif1 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") cif2 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1/cif2
A toi d'adapter après avec les valeurs dont tu as besoins ;)
Attention, ta méthode est la bonne mais tu oublies un détail :
Code:

1 2 3 4 5 If cif2 <> 0 then Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1/cif2 Else Sheets("Ta feuille finale").Range("Ta cellule finale").Value = 0 'A changer si tu veux autre chose End If
Par sécurité c'est mieux ça évite les erreurs, autant la formule Excel mettrait un #N/A autant la macro elle plantera. Enfin de toute façon c'est plus propre.

DeathZarakai.

17/05/2016, 11h10
mateoa44

Bonjour à vous deux !

Je vous remercie pour vos réponses !

Je me repenche sur le problème cet après-midi, je vous tiendrais au courant !

Citation:
Envoyé par DeathZarakai
Attention, ta méthode est la bonne mais tu oublies un détail :
Code:

1 2 3 4 5 If cif2 <> 0 then Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1/cif2 Else Sheets("Ta feuille finale").Range("Ta cellule finale").Value = 0 'A changer si tu veux autre chose End If
Par sécurité c'est mieux ça évite les erreurs, autant la formule Excel mettrait un #N/A autant la macro elle plantera. Enfin de toute façon c'est plus propre.

DeathZarakai.
En effet mais je partais du principe que son cif2 ne serait jamais égale à 0
Mais tu as raison il vaut mieux le préciser :mrgreen:

PS : il me semble par ailleurs l'erreur que mettrait excel est #DIV/0! non ?

17/05/2016, 12h30
DeathZarakai

Citation:

Envoyé par Al__22

PS : il me semble par ailleurs l'erreur que mettrait excel est #DIV/0! non ?

Après test en effet, mais étant matheux c'est un truc que je check toujours direct, donc j'avais jamais eu le cas :p
17/05/2016, 12h33
joe.levrai

Bonjour,

je pense plutôt que la procédure va planter avec l'erreur "Division par 0" si cif2 = 0 et pas cif1 ?
on aura un dépassement de capacité si les 2 ont une valeur à 0

ce n'est qu'un détail ... certes :aie:
17/05/2016, 12h36
Kiouane

Il a bien mis la condition pour le cif2 Joe!

Citation:

Envoyé par DeathZarakai

Après test en effet, mais étant matheux c'est un truc que je check toujours direct, donc j'avais jamais eu le cas :p

Quelle horreur comment peut on aimer les maths ? :aie:

Bon sinon en 3 lignes avec les modifs ça donnerait :
Code:

1 2 3 cif1 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") cif2 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") If cif2 <> 0 Then Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1 / cif2 Else Sheets("Ta feuille finale").Range("Ta cellule finale").Value = 0

17/05/2016, 14h16
joe.levrai

Citation:

Envoyé par Kiouane

Il a bien mis la condition pour le cif2 Joe!

Je rebondissais uniquement sur le message qui indiquait que le renvoie de valeur allait générer un #DIV/0 si la formule était appliquée depuis Excel, en complétant sur les erreurs de procédures qui allaient tomber côté VBA
17/05/2016, 15h45
mateoa44

Je ne me suis toujours pas remis sur l'automatisation, je remplie un peu mon rapport de stage hehe !!

Merci beaucoup à vous tous pour votre participation, je regarde tout ça demain finalement !!! Je vous tiens au courant, c'est promis !

Citation:
Envoyé par Al__22
Quelle horreur comment peut on aimer les maths ? :aie:

Bon sinon en 3 lignes avec les modifs ça donnerait :
Code:

1 2 3 cif1 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") cif2 = Application.WorksheetFunction.CountIf(Sheets("Ta feuille avec les données").Range("Ton range"), "Ta condition") If cif2 <> 0 Then Sheets("Ta feuille finale").Range("Ta cellule finale").Value = cif1 / cif2 Else Sheets("Ta feuille finale").Range("Ta cellule finale").Value = 0
Alors, j'ai fais comme vous m'avez suggéré :
Code:

1 2 3 4 5 6 Dim nbsi1 As String, nbsi2 As String With Sheets("Synthese") .Range("E12").NumberFormat = "0"" des PDV avec demandes de rappel respectent l'engagement""" nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & Sheets("Telephonie").Range("P4").End(xlDown).Row), "=100%") nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & Sheets("Telephonie").Range("O4").End(xlDown).Row), ">0") If nbsi2 <> 0 Then .Range("E12").Value = (nbsi1 / nbsi2) * 100 Else .Range("E12").Value = 0
Il me met un résultat de 0.25 soit 25%
Or, lorsque je fais le calcul à la main : nbsi1 = 9 et nbsi2 = 14 ==> 9/14 = 0.65 le résultat devrait donc être de 65% ^^
Vous avez une idée ? ^^

EDIT :

Je pense qu'au lieu de faire 9/14 il fait 1/4 ==> en effet, dans les deux colonnes (P et O) il y a des cases vides, pour P, il y a 100% puis des cases vides puis d'autres 100%, je pense qu'il prend seulement le premier 100,et pour la colonne O, il prend toutes les valeurs >0 avant la première case vide

18/05/2016, 11h04
Kiouane

Tu peux mettre en pièce jointe ton fichier? Je voudrais essayer de comprendre l'erreur.
18/05/2016, 11h14
mateoa44

1 pièce(s) jointe(s)

Baaaah, j'peux pas :x

C'est considéré comme confidentiel ...

Je peux te montrer ceci au maximum ..
Pièce jointe 210010

la valeur du nb.si (sur la colonneP) / nb.si (sur la colonne O) * 100 doit être mise dans une autre feuille sur E12

Cherche pas c'est parce qu'il y a des trous :mrgreen:
Du coup le end(xldown) ne peux pas fonctionner il s’arrête au premier trou

Essaye en remplaçant dans ton code les :

Code:

1
2
nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & Sheets("Telephonie").Range("P4").End(xlDown).Row), "=100%")
nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & Sheets("Telephonie").Range("O4").End(xlDown).Row), ">0")

Par, pour exemple :

Code:

1
2
nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P10000"), "=100%")
nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O100000"), ">0")

18/05/2016, 11h19
Kiouane

Comment tu délimites ta plage de données? Est ce que la dernière ligne peut contenir une case vide? Si oui est ce que tu peux me dire si le tableau contient d'autres colonnes où les données sont remplies pour toutes les lignes jusqu'à la dernière.

C'est ce que j'avais cru comprendre oui ^^
Pas moyen de régler ça ?
avec un truc dans le genre ?
Code:

1 2 3 4 dim dern as long 'dernière ligne dim cpt as long 'compteur dern = Range("B1000000").End(xlUp).row for cpt = 4 to dern
Je ne sais pas comment adapter ceci aux formules des nbsi1 et nbsi2

Citation:

Envoyé par Kiouane

Comment tu délimites ta plage de données? Est ce que la dernière ligne peut contenir une case vide? Si oui est ce que tu peux me dire si le tableau contient d'autres colonnes où les données sont remplies pour toutes les lignes jusqu'à la dernière.

Le tableau contient d'autres colonnes oui, mais toutes les colonnes ne contiennent pas de cases vide, par exemple la colonne B ne contiendra JAMAIS de cases vides

EDIT : Problème résolue il me semble !
Code:

1 2 3 nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & Sheets("Telephonie").Range("B1000000").End(xlUp).Row), "=100%") nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & Sheets("Telephonie").Range("B1000000").End(xlUp).Row), ">0") If nbsi2 <> 0 And nbsi2 <> "" And nbsi1 <> "" Then .Range("E12").Value = (nbsi1 / nbsi2) * 100 Else .Range("E12").Value = 0

Citation:

Envoyé par mateoa44

Le tableau contient d'autres colonnes oui, mais toutes les colonnes ne contiennent pas de cases vide, par exemple la colonne B ne contiendra JAMAIS de cases vides

Du coup c'est simple :
Code:

1 2 nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & Sheets("Telephonie").Range("B4").End(xlDown).Row), "=100%") nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & Sheets("Telephonie").Range("B4").End(xlDown).Row), ">0")

Justement j'allais te proposer celà :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
 
n= WorkSheets("Telephonie").Range("B"&Rows.count).end(xlup).row
 
Dim nbsi1 As String, nbsi2 As String
 
With Worksheets("Synthese")
 
 .Range("E12").NumberFormat = "0"" des PDV avec demandes de rappel respectent l'engagement"""
        nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & n), "=100%")
        nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & n), ">0")
        If nbsi2 <> 0 Then .Range("E12").Value = (nbsi1 / nbsi2) * 100 Else .Range("E12").Value = 0
 
 
End Sub

Je pense que les deux façons que vous m'avez proposé fonctionnent, merci à vous :)
Je sais pas si je laisse comme j'ai écris avec
Code:

1 2 3 nbsi1 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("P4:P" & Sheets("Telephonie").Range("B1000000").End(xlUp).Row), "=100%") nbsi2 = Application.WorksheetFunction.CountIf(Sheets("Telephonie").Range("O4:O" & Sheets("Telephonie").Range("B1000000").End(xlUp).Row), ">0") If nbsi2 <> 0 Then .Range("E12").Value = (nbsi1 / nbsi2) * 100 Else .Range("E12").Value = 0
Ou bien si je prend une de vos solutions, pensez-vous que celle que j'ai écrite juste ici est suffisante ?

18/05/2016, 11h33
DeathZarakai

Parce que cela fait 3 fois que je me fais taper les doigts pour ça, je vais me venger :lol:

Code:

dern = Range("B1000000").End(xlUp).row

Fonctionnera pas sur des versions précédentes, il faut prendre une écriture tel:

Code:

dern = Range("B" & Rows.Count).End(xlUp).row

DeathZarakai.
18/05/2016, 11h34
Kiouane

Tu peux garder la tienne mais avec la mienne tu évites l'erreur au cas où t'es sur une version ancienne d'Excel. Entre autre, pour une meilleure visibilité de ton code déclare une variable comme pour ma solution où tu injecteras le numéro de la dernière ligne.
18/05/2016, 11h49
mateoa44

Ok Zarakai, je vais aller modifier d'autres codes où j'ai mis ça 8O 8O
Merci pour l'info :P

@Kiouane : ça marche, je vais prendre ta solution alors, je te remercie !

Je clôture le sujet, merci à vous tous pour votre participation !
Et je mets des petits pouces verts aussi :)

Fuseau horaire GMT +2. Il est actuellement 06h19.