Application d'une formule NB.SI.ENS complexe en VBA

Le 08/10/2020 à 09:49

Bonjour à tous,

Je viens vers vous avec une colle que je n'ai pas su résoudre même en cherchant sur le net.

Certains trouveront ça certainement facile. mais bon, j'en ai assez de chercher sans succès.

Contrainte : je dois utiliser la fonction à partir de VBA car le fichier à traiter a plus de 35 000 lignes. et une formule appliquée à chaque cellule de la colonne K prend énormément de temps à calculer. Voici la macro du fichier :

Sub Formule()

Dim cell As Range

    Set cell = Range("K2")

    'formule qui ne donne pas le même résultat que sous la forme excel
    MsgBox WorksheetFunction.CountIfs(cell.Offset(0, -2).EntireColumn, "Oui", _
    cell.Offset(0, 1).EntireColumn, cell.Offset(0, 1).Value, _
    cell.Offset(0, 2).EntireColumn, ">=" & (cell.Offset(0, -8).Value + 0.01), _
    cell.Offset(0, 2).EntireColumn, "<" & (cell.Offset(0, -8).Value + Range("A1").Value))

    Exit Sub '< pour ne pas lancer la boucle ci-dessous

   ' formule à appliquer sous la forme de boucle.
    For Each cell In Range("K2:K" & Range("A1").CurrentRegion.Rows.Count)
        cell.Value = WorksheetFunction.CountIfs(cell.Offset(0, -2).EntireColumn, "Oui", _
        cell.Offset(0, 1).EntireColumn, cell.Offset(0, 1).Value, _
        cell.Offset(0, 2).EntireColumn, ">=" & (cell.Offset(0, -8).Value + 0.01), _
        cell.Offset(0, 2).EntireColumn, "<" & (cell.Offset(0, -8).Value + Range("A1").Value))
    Next

    ' formule sous la forme Excel (à copier en "K2" et étendre jusqu'à la currentregion)
    '=SI(I2="Oui";NB.SI.ENS(I:I;"Oui";L:L;L2;M:M;">="&C2+0,01;M:M;"<"&C2+$A$1);"")

End Sub

Ma problématique c'est que l'application de la formule en VBA ne me ressort pas la même valeur que la formule sous excel.
Quelqu'un peut m'aider à trouver pourquoi ?

Merci à tous !

44formule-nb-si-ens.xlsm (34.31 Ko)
Le 08/10/2020 à 09:50

personne ne peut m'aider ??

Le 08/10/2020 à 14:42

Salut salmanasard,

Pourtant, internet précise bien que pour une formule, la fonction est un peu différente !

Petit rappel :

Il y a 4 méthodes pour insérer une formule dans une cellule via VBA

Range("A1").Formula --> écrire la formule, en utilisant les références du style A1, dans la langue de VBA (anglais) et elle s'adaptera à la langue de l'application

Range("A1").FormulaLocal --> écrire la formule, en utilisant les références de style A1, dans la langue de l'application, dans ton cas le français (va planter si on passe le fichier sur un Excel d'une autre langue)

Range("A1").FormulaR1C1 --> écrire la formule, en utilisant les références du style R1C1, dans la langue de VBA

Range("A1").FormulaR1C1Local --> écrire la formule, en utilisant les références du style R1C1, dans la langue de l'application

Tu auras donc dans ton code VBA, la boucle suivante :

    For Each cell In Range("K2:K" & Range("A1").CurrentRegion.Rows.Count)
        cell.FormulaLocal = "=SI(I" & cell.Row & "=""Oui"";NB.SI.ENS(I:I;""Oui"";L:L;L" & cell.Row & ";M:M;"">=""&C" & cell.Row & "+0,01;M:M;""<""&C" & cell.Row & "+$A$1);"""")"
    Next

Bonne journée !

Baboutz

Le 08/10/2020 à 14:53

C’est là que le bat blesse. je ne cherche pas à renseigner la formule dans une cellule.

Je cherche à retourner la valeur de la fonction.

La formule que j’ai saisi sous excel fonctionne (vérifications faites)

la formule en VBA me renvoi une autre valeur.

pourquoi ?

(il y a un loup, mais je ne trouve pas où...)

Le 08/10/2020 à 19:42

Re,

À quoi correspond le chiffre "5" en A1 s'il te plaît ?

C'est un problème de type de fonction

Le 08/10/2020 à 19:54

J'ai un peu corrigé ta formule, maintenant on obtient "4", je ne sais pas où est l'erreur...

    MsgBox WorksheetFunction.CountIfs(Range("I:I"), "Oui", Range("L:L"), Range("L" & cell.Row).Value, Range("M:M"), ">=" & CLng(Range("C" & cell.Row).Value + 0.01), Range("M:M"), "<" & CLng(Range("C" & cell.Row).Value + Range("A1").Value))

Je viens de remarquer que lorsqu'on je fais des tests avec la ligne 3, j'obtiens 2 à la place de 1, avec la ligne 4 : 0 (Donc bon résultat).

Pour une raison que j'ignore, vba double le résultat... Je me demande si ce n'est pas un problème de fonction de type. J'ai mis CLng pour traiter ces données sous le type Long mais ce n'est peut-être par le bon... Hum...

ÉDIT : Après test ce n'est pas ça...

Le problème vient de cette partie du code ça c'est sur :

">=" & CLng(Range("C" & cell.Row).Value + 0.01), Range("M:M"), "<" & CLng(Range("C" & cell.Row).Value + Range("A1").Value)

Mais je ne sais pas exactement le problème... Je crois que c'est le 0.01 qui gêne mais je ne suis pas certain

Le 08/10/2020 à 20:27

j’ai testé sans le 0,01. idem.

J’ai testé avec <=, avec <, avec >=, avec >... idem

La formule appliquée sous excel fonctionne. (vérification minutieuse à l’appui) mais sous VBA avec un ‘’CountIfs” ça ne renvoie pas la même valeur.

(J’avais prévenu que c’était un défi digne de ce nom ! 😁)

Le 08/10/2020 à 20:45

Sinon, on triche ? 

WorksheetFunction.CountIfs(Range("I:I"), "Oui", Range("L:L"), Range("L" & cell.Row).Value, Range("M:M"), ">=" & CLng(Range("C" & cell.Row).Value + 0.01), Range("M:M"), "<" & CLng(Range("C" & cell.Row).Value + Range("A1").Value)) / 2
Le 08/10/2020 à 21:00

CLng

je ne connais pas. C’est quoi ? (je pourrai chercher sur le net pour me la jouer grand-sachant mais c’est pas mon genre.)

Le 08/10/2020 à 21:23

C'est pour convertir en Long. Sinon vba comprend que tu additionnes une date et un décimal (0.01) et ne comprend pas. C'est pour ça que ça t'affichait 0 avant

Le 08/10/2020 à 21:34

Pour chaque plage + critère, j'ai effectué un NB.SI en VBA pour voir si le résultat est le bon :

  • MsgBox WorksheetFunction.CountIf(Range("I:I"), "Oui", Range("L:L")) renvoie 9. C'est la bonne réponse, pas un problème, on élimine.
  • MsgBox WorksheetFunction.CountIf(Range("L:L"), Range("L" & cell.Row).Value) renvoie 9. C'est la bonne réponse, pas un problème, on élimine.
  • MsgBox WorksheetFunction.CountIf(Range("M:M"), ">=" & CLng(Range("C" & cell.Row).Value + 0.01)) renvoie 6. PROBLÈME, c'est normalement 3 !
  • MsgBox WorksheetFunction.CountIf(Range("M:M"), "<" & CLng(Range("C" & cell.Row).Value + Range("A1").Value)) renvoie 10. C'est la bonne réponse, pas un problème, on élimine.

La problème vient donc du troisième plage + critère qui, pour une raison que j'ignore, compte le double de ce qui devrait normalement être la bonne réponse...

Le 08/10/2020 à 22:20

Hum.

J'ai à la fois envie de pleurer de tristesse et de joie...

Je pense avoir la solution. Le problème était bien le &3?;§!# de 0.01

En mettant 0.5, dans la formule Excel et dans le code VBA, on obtient le même et bon résultat... 0.01 sûrement une trop petite valeur.

J'espère que le 0.01 n'était pas choisi au hasard et que 0.5 ne dérange pas dans votre application !

Voici donc la bonne formule :

MsgBox WorksheetFunction.CountIfs(Range("I2:I" & fin), "Oui", Range("L2:L" & fin), Range("L" & cell.Row).Value, Range("M2:M" & fin), ">=" & CLng(Range("C" & cell.Row).Value + 0.5), Range("M2:M" & fin), "<" & CLng(Range("C" & cell.Row).Value + Range("A1").Value))

Bonne soirée,

Baboutz

Le 09/10/2020 à 10:09

je teste ça cet aprem. en tout cas merci !

Le 10/10/2020 à 04:40

ça marche paaaaas !

En "K3", la valeur renvoyée n'est pas bonne.. =1 via la formule EXCEL =2 via la formule VBA.

123

lol.

Je vais devoir avancer dans ma programmation mais je vais encore chercher un peu. Si je ne trouve pas, je contournerai le problème.

Le 10/10/2020 à 10:35

Bonjour,

Si vraiment countifs ne fonctionne pas, il y a peut-être moyen de passer par sommeprod :

resultat = worksheetfunction.sumproduct(([L:L]="Oui")*(...)*(....)*1)

Sait-on jamais.

Rechercher des sujets similaires à "application formule ens complexe vba"