Compter le nombre de cellules ne contenant pas de formule

[jpriouzeau]

Bonjour,

Auriez-vous une idée pour l'établissement d'une formule qui me permettrait de compter le nombre de Cellules dans une plage déterminée (ex A1:Z30) dont le contenu n'est pas une formule ?

Je sèche et j'espère que ma question est assez précise.

Merci d'avance,

Julien

 

[Dugenou]

Re : Compter le nombre de cellules ne contenant pas de formule

Bonjour jp,

Il y a une méthode un peu bizarre.

Tu crées une fonction nommée (par insertion/nom/définir) qui utilise la fonction LIRE.CELLULE (6;adresse). Cette fonction permet d'afficher la formule quand il y en a une.
Ensuite un petit test : si le résultat commence par = alors c'est une formule.
Voir dans fichier joint : l'avertissement sur les macros excel 4 est normal.

 

[jpriouzeau]

Re : Compter le nombre de cellules ne contenant pas de formule

Salut Dugenou,

Je comprends pas ta "fonction nommée (par insertion/nom/définir) qui utilise la fonction LIRE.CELLULE (6;adresse)"

Je sais bien sélectionner une plage de cellule pour la nommer mais je vois pas le rapport. Ensuite, dès que je touche à ta formule, ça me renvoie une erreur.

D'autre part, ce comptage est à faire sur des centaines de cellules (voir des milliers) donc en partant sur ton principe, je devrai créer une feuille copie qui me renverrait "formule" ou "valeur" dans la cellule correspondante. Ainsi, effectivement, je saurai compter le nombre de cellules "Valeur" mais mon fichier est déjà gros et je ne veux pas le surcharger. Quitte à passer par un tableau copie, je changerai toute ma méthode de calcul.

N'y a-t-il pas une méthode directe qui va me donner ce nombre de cellules ?

Merci du coup de main en tout cas.

 

[pierrejean]

Re : Compter le nombre de cellules ne contenant pas de formule

bonjour jpriouzeau

Salut Dugenou

Avec une fonction personnalisée

 

[MJ13]

Re : Compter le nombre de cellules ne contenant pas de formule

Bonjour jpriouzeau, dugenou, PJ


Teste ce fichier:

 

[jpriouzeau]

Re : Compter le nombre de cellules ne contenant pas de formule

Merci Pierre-Jean,
Effectivement, ta fonction personnalisée fonctionne. Comment dois-je faire pour l'intégrer dans mon classeur.
Je n'ai jamais créé de formules personalisées. Quelle est le code que tu as utilisé pour la définir ?

MJ13,
Pas mal la macro, par contre, je ne souhaite pas intégrer de boutons ou Userform dans mon fichier, et surtout, ce calcul doit se faire sur plusieurs cellules, donc j'ai plusieurs valeurs à renvoyer.

A+

Julien

 

[pierrejean]

Re : Compter le nombre de cellules ne contenant pas de formule

Re

Ayant testé (sous 2007) le fichier de MJ13 , j'ai du faire de petites modifications pour utiliser successivement les 2 boutons
Les macros n'en sont pas moins du plus haut interet

@ jpriouzeau
Dans mon fichier tu fais CTRl +F11
tu te trouveras dans Vba Editor
Copie la fonction
Dans ton fichier : Ctrl+ F11 Eventuellement Insertion Module Colle la fonction
Ensuite elle est accessible comme toutes les autres fonctions dans le paragraphe fonctions personnalisées

 

[jpriouzeau]

Re : Compter le nombre de cellules ne contenant pas de formule

Merci beaucoup Pierre-Jean,

En plus de solutionner mon problème, tu viens de m'ouvrir la porte sur des possibilités d'Excel auxquelles je ne m'étais jamais intéressé (la création de formules personnalisées)

Je peux clore le sujet pour problème résolu !

Merci à tous de votre aide, super communauté !

julien

 

[Staple1600]

Re : Compter le nombre de cellules ne contenant pas de formule

Bonsoir à tous

Comme c'est fait je poste

Une autre fonction personnalisée

Sub test()
Dim p As Range, c As Range, compte&
Set p = ActiveSheet.UsedRange
For Each c In p
If SansFormule(c) And Not IsEmpty(c) Then compte = compte + 1
Next
MsgBox compte
End Sub

Function SansFormule(r As Range)
SansFormule = Not r.HasFormula
End Function

 

[jpriouzeau]

Re : Compter le nombre de cellules ne contenant pas de formule

Re à tous,

Bon je viens corser un peu le sujet...

Et si maintenant je veux faire la somme des valeurs contenues dans toutes les cellules ne contenant pas de formule, quelle serait la modif applicable sur la fonction personnalisée de PierreJean ?

La voici pour rappel :

Function nb_cellules_sans_formule(plage)
Application.Volatile
For Each cel In plage
If Left(cel.Formula, 1) = "=" Then nb = nb + 1
Next
nb_cellules_sans_formule = plage.Count - nb
End Function

 

[pierrejean]

Re : Compter le nombre de cellules ne contenant pas de formule

Re

Voila

Function somme_cellules_sans_formule(plage)
Application.Volatile
For Each cel In plage
 If Left(cel.Formula, 1) <> "=" Then somme = somme + cel.Value
Next
somme_cellules_sans_formule = somme
End Function

Et mes amitiés à Jean-Luc

 

[jpriouzeau]

Re : Compter le nombre de cellules ne contenant pas de formule

Merci beaucoup PierreJean,

Cette modif vient en plus de me faire comprendre à quoi servait le code après la balise Next.

C'est qui Jean-Luc ? ;-)