Utilisation formule Excel en VBA

Bonjour,

Je cherche actuellement un moyen pour compter le nombre d’éléments différent dans une plage définie. Plutôt que de partir dans une ou plusieurs boucle pour arriver à mes fins, j'ai préféré utiliser les fonctions d'Excel pour parvenir à mes fins. Voici ce que j'obtiens en Excel.

=SOMMEPROD(1/NB.SI(F270:F272;F270:F272))

Jusqu'ici pas de problème. A présent, il faut que j'arrive à utiliser cette formule Excel en code VBA :

Code:

---

1 2 3 4 5 6

Dim nbPoste As Double Dim rg As Range   Set rg = Sheets(1).Range("F270:F272") nbPoste = WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(rg, rg))

---

Cependant, j'obtiens une erreur d'exécution '13': Incompatibilité de type à la dernière ligne alors que j'ai vérifié que les deux fonctions retournent bien un Double.

J'ai aussi essayé en ne gardant que le WorksheetFunction.CountIf(rg,rg) mais la même erreur revient.

En espérant que quelqu'un puisse m'aider. :)

Bonjour,
Le deuxième argument de CountIf est une condition, quelle est la condition dans ton cas?

Dans mon cas, la condition est qu'il y est la valeur "M" ou "N" ou "S" de présente dans la plage.

Bonsoir
utilise l'enregistreur automatique et tu aura ton code sur plateau :aie:
Bonne continuation

Bonsoir,
Voici ce que j'obtiens avec l'enregistreur de macro :

SUMPRODUCT(1/COUNTIF(R[-2590]C:R[-2588]C,R[-2590]C:R[-2588]C))

Je suis donc sur la bonne voie mais le CountIf est assez étrange. :?

voila un exemple :
dans mon cas je copie cette formule dans la colonne M depuis M3 à le dernière ligne non vide :

Code:

---

1 2 3 4 5 6 7

Dim DL as long DL = .Range("A" & Rows.Count).End(xlUp).Row Range("M3").Select ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R3C12:RC12,RC[-1])>COUNTIF(R3C3:R" & DL & "C3,RC[-1]),RC[-1],"""")" Range("M3").Select Selection.AutoFill Destination:=Range("M3:M" & DL), Type:=xlFillDefault Range("M3:M" & DL).Select

---

Je vais vous présenter mon sujet de manière plus globale pour comprendre l'utilisation de cette formule dans mon cas:

J'ai un fichier de base composé d'un tableau de 76 colonnes et énormément de lignes. Chaque ligne indique une quantité produite par poste durant une journée. Il existe trois types de poste M, S et N. Dans mon tableau, c'est la colonne F qui indique qu'elle est le poste concerné par la saisie.
L'un des buts de mon outil est d'indiquer le temps de fonctionnement de la chaîne de production sur une journée indiquée par l'utilisateur.
Par exemple, si l'utilisateur indique le 15/05/2017, ma macro applique un filtre sur le tableau Excel pour obtenir que les saisies de production effectuées le 15/05/2017. Imaginons qu'il y a eu 3 saisies de production le 15/05/2017, dans le cas le plus général on aura tout les postes de productions qui ont été utilisés, on aura donc d'afficher :
M
S
N

Donc, le temps de fonctionnement sera égal à 3 x temps de production par chaîne. Mais il est aussi possible qu'on est d'afficher ceci :

M
M
N

Donc, ici il n'y a que 2 postes d'utiliser, soit 2 x temps de production par chaîne.

C'est pour cela que je cherche à compter les nombre de valeurs uniques dans la colonne F, résultat que je dois récupérer en VBA pour continuer le reste de mes calculs (il y en a beaucoup d'autre de ce genre).

Ca ne répond pas vraiment à ta question, mais est-ce que ce ne serait pas équivalent à vérifier si tu trouves M, S et N. Par exemple, si tu trouves M et S, mais pas N, tu sais qu'il y a 2 saisies différentes. A condition que vous ne décidiez pas tout d'un coup de n'avoir plus que 2 postes par jours ;)

Je pense que c'est une idée qui marcherait, as-tu une piste pour la développer ? :P

Si j'ai bien compris le but de ton travail, un tableau dynamique croisé avec des segment fais bien l'affaire sans être besoin de recourir à VBA.
dans Ton tableau tu peux ajouter une colonne pour le mois

Citation:

---

TEXTE(MOIS.DECALER(A7;0);"mmmm")

---

une autre colonne pour l'année et ces colonnes seront utilisées comme segment
la poste sera aussi un segment

Tu peux regarder du côté de Application.Match (ou WorksheetFunction.Match) qui permet de trouver une valeur. Ou la méthode Find.
Après, je ne suis pas sûre de leur comportement après filtrage du tableau...donc à tester.
De mon côté, c'est le début du week-end maintenant, alors je vais te laisser chercher .... et je reviendrai la semaine prochaine. Peut-être que quelqu'un d'autre pourra t'aider à continuer d'ici là.
Bon courage ;)

Bonjour,

Citation:

---

C'est pour cela que je cherche à compter les nombre de valeurs uniques dans la colonne F

---

La réponse peut-être dans ce billet intitulé Comment compter le nombre de valeurs distinctes dans une colonne d’excel

Citation:

---

résultat que je dois récupérer en VBA pour continuer le reste de mes calculs

---

la lecture de ce billet Ecrire une formule dans Excel à l'aide d'une procédure VBA et sa suite éventuellement Comment écrire une formule plus complexe dans une cellule ? te permettra sans doute de résoudre ton problème.

Bonjour !

Citation:

---

Envoyé par Oudouner

=SOMMEPROD(1/NB.SI(F270:F272;F270:F272))

---

Convertir la formule en version native donc en anglais (ou directement sa traduction via la propriété  Formula  )

puis juste utiliser la puissante méthode   Evaluate   !   :fleche:   V = [SUMPRODUCT(1/COUNTIF(F270:F272,F270:F272))]   …

Et si la feuille de calculs n'est pas active, rattacher cette méthode à la feuille, voir l'aide VBA interne.

Autre point :   ne jamais utiliser WorksheetFunction sauf pour être sûr de planter le code en cas d'erreur de la formule ‼

Application étant suffisant …

___________________________________________________________________________________________________________

   :arrow:  Merci de cliquer sur :plusser: en bas à droite de chaque message ayant aidé puis sur :resolu: pour clore cette discussion …

___________________________________________________________________________________________________________

Je suis Paris, Manchester, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Bonsoir,

Merci beaucoup de votre aide. J'ai testé la méthode de Marc-L

Code:

---

1 2 3 4 5

Dim nbPoste As Variant   nbPoste = [SUMPRODUCT(1/COUNTIF(F270:F272,F270:F272))] nbPoste = Evaluate("SUMPRODUCT(1/COUNTIF(F270:F272,F270:F272))")

---

Cependant ma variable nbPoste prend la valeur "Erreur 2007". :(

La feuille de calculs de la plage de la formule est-elle active ?

Si oui, quel est le résultat de la même formule dans une cellule ?

Bonjour,

J'ai enfin réussi à obtenir le bon résultat. Le seul problème est que je dois garder la feuille de calcul de la plage de la formule visible alors que j'aurai préféré qu'elle soit non visible (pour ne pas gêner l'utilisateur).

Merci beaucoup de votre aide à tous. :)

Bonjour,

Citation:

---

Le seul problème est que je dois garder la feuille de calcul de la plage de la formule visible alors que j'aurai préféré qu'elle soit non visible (pour ne pas gêner l'utilisateur)

---

J'écris et lit des formules se trouvant dans des feuilles non actives et cachées sans aucun problème mais pour cela il faut donner la référence totale aux cellules concernées (feuille + classeur)
Si tu avais lu les billets que je t'ai référencé tu aurais pu le réaliser sans peine.

Bonjour Marc,

Citation:

---

Autre point : ne jamais utiliser WorksheetFunction sauf pour être sûr de planter le code en cas d'erreur de la formule ‼
Application étant suffisant …

---

J'avoue ne pas comprendre cette assertion.
Que l'on utilise Application.NomDeLaFonction ou Application.WorksheetFunction.NomDeLaFonction, dans les deux cas de figure nous pouvons avoir un renvoi d'erreur que de toutes manières nous devrons gérer.

Dans un cas, on peut utiliser le IsError mais cela à condition d'avoir déclarer la variable recevant le résultat de la fonction comme Variant alors que dans l'autre cas, il suffit d'utiliser le On Error resume Next pour récupérer le n° de l'erreur.

Personnellement j'utilise WorksheetFunction et je ne rencontre aucun problème dans la gestion des erreurs. L'autre avantage c'est que l'on bénéficie de l'auto-complétion

Cas 1 avec Application.Match

Code:

---

1 2 3 4 5 6 7 8 9

Sub t_1() Dim ColumnNumber As Variant ColumnNumber = Application.Match("Label 3", Range("B1:H1"), 0) If IsError(ColumnNumber) Then MsgBox "Pas trouvé" Else MsgBox "Trouvé colonne " & ColumnNumber End If End Sub

---

Cas 2 avec Application.WorksheetFunction.Match

Code:

---

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

Sub t_2() Dim ColumnNumber As Integer, fx As WorksheetFunction Set fx = Application.WorksheetFunction On Error Resume Next ColumnNumber = fx.Match("Label 30", Range("B1:H1"), 0) If Err Then MsgBox "Pas trouvé" Else MsgBox "Trouvé colonne " & ColumnNumber End If On Error GoTo 0 End Sub

---

Oui WorksheetFunction induit  - oblige -  l'utilisation d'une gestion d'erreur via On Error
or c'est plus simple pour un débutant  - hormis effectivement l'autocompletion -  car sans On Error via Application seule
juste en testant le résultat via IsError or IsNumeric …
Pour Application seule si utilisation d'une variable alors elle est doit être de type Variant en cas d'erreur retournée.

Donc pour quelqu'un ignorant mes recommandations et affirmant qu'il ne peut y avoir d'erreur avec sa formulation
alors je lui livre un code utilisant WorsheetFunction qui forcément lui prouvera le contraire dès la première erreur rencontrée !

Sinon dans un cas normal je n'utilise plus WorksheetFunction, Application seule produisant le même résultat et en souplesse !

Les goûts, les couleurs … :lun:

Bonjour Marc,

Citation:

---

Les goûts, les couleurs …

---

Je suis d'accord avec toi.
Ce qui m'a fait réagir c'est la phrase "ne jamais utiliser WorksheetFunction sauf pour être sûr de planter le code en cas d'erreur de la formule" qui sous-entendait que l'utilisation du seul Application ne provoquerait pas le plantage du code en cas d'erreur.

En cas d'une fonction retournant un résultat de type Error et sans On Error
pas de plantage avec Application combinée si besoin à une variable Variant
mais via WorksheetFunction l'exécution s'arrête net avec un joli message !

Donc tant que On Error peut être évitée, ne pas utiliser WorksheetFunction …