Formule NBVAL sur prog excel VBA

Résolu

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention -
manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention - 1 janv. 2012 à 22:15

Bonjour,

J'ai une programmation toute simple à créer sur Excel VBA mais je bloque, vu que c'est ma première expérience.

J'ai un fichier dont les lignes varient selon les mois. Chaque mois, je voudrais compter le nombre de ligne présent dans le fichier.

Sur VBA, j'ai donc commencer à écrire :

Dim DernierNoLig As Integer
Dim NoLigDuCumul As Integer

DernierNoLig = Range("A1").CurrentRegion.Rows.Count
NoLigDuCumul = DernierNoLig + 1

Do While Not (IsEmpty(NoLigDuCumul))
Cells(NoLigDuCumul, 2).FormulaR1C1 = "=CountA (R[1]C2:R[-1]C2)"
Loop

VBA m'indique une erreur au niveau de la ligne en gras et je n'arrive pas à la déceler.

Quelqu'un pourrait-il m'aider ?
Merci d'avance.

Afficher la suite

Répondre (13) Posez votre question

Répondre (13)

A voir également:

Vba nbval
Nbval en anglais - Guide
L'indice n'appartient pas à la sélection vba - Forum VB / VBA
Incompatibilité de type vba ✓ - Forum Programmation
Excel compter cellule couleur sans vba - Guide
Vba attendre 1 seconde ✓ - Forum VB / VBA

13 réponses

Réponse 1 / 13

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

enlève les espaces superflus :
Cells(NoLigDuCumul, 2).FormulaR1C1 = "=CountA(R[1]C2:R[-1]C2)"

Mais tu vas rencontrer un autre problème sur :
Do While Not (IsEmpty(NoLigDuCumul))
Ctrl+Pause pour arreter une macro qui boucle indéfiniment...

eric

Réponse 2 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Bonjour,

Merci pour ta réponse rapide.

Il est vrai qu'hier, ma boucle s'exécutait sans fin. Mais je ne sais pas comment résoudre le problème. Je ne sais pas quoi mettre à la place.

Merci d'avance

Manue

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

Impossible sans plus de précision sur ce que tu veux faire, sinon je te l'aurai dit hier...
Y'a-t-il un ou plusieurs bloc de données à parcourir ? En quele colonne ? etc
Fichier exemple sur cjoint.com et coller ici le lien fourni

eric

Réponse 3 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Re

En fait, je viens de comprendre que je n'avais pas besoin de boucle puisque j'ai mon nombre de ligne grâce à ma variable "DernierNoLig".

Maintenant, je n'arrive pas à créer la formule. ça devrait être une formule du genre : DernierNoLig -1

Je te remet mon code VBA ci-dessous et j'essai de te joindre mon fichier :

Dim DernierNoLig As Integer
Dim NoLigDuCumul As Integer

DernierNoLig = Range("A1").CurrentRegion.Rows.Count
NoLigDuCumul = DernierNoLig + 1

Cells(NoLigDuCumul, 2).Formula = "=DernierNoLig -1"

Lien du fichier : https://www.cjoint.com/?3LFnJzUR0EI

Merci d'avance.
Manue

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Je ne vois pas où sont les explications...
eric

Réponse 4 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Oups, désolée

'Permet de copier-coller le fichier que j'ai à traiter

Sheets("fichier de base").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("base valabs").Select
ActiveSheet.Paste
Range("L1").Select
Application.CutCopyMode = False

'Rajout de 3 colonnes : "VALABS", "% VALABS" et "% VALABS CUMULE"
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "VALABS"
Range("M1").Select
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "% VALABS"
Range("N1").Select
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "% VALABS CUMULE"

'Définition des variables dont j'ai besoin pour mes calculs
'DernierNoLig : Dernier n° de ligne du fichier
'NoLigDuCumul : ligne suivante où je souhaite insérer une formule en colonne B
Dim DernierNoLig As Integer
Dim NoLigDuCumul As Integer

DernierNoLig = Range("A1").CurrentRegion.Rows.Count
NoLigDuCumul = DernierNoLig + 1

' Dans le cellule (Bx) insérer la formule NBVAL(Bx:B2)
Cells(NoLigDuCumul, 2).Formula = "=DernierNoLig -1"

End Sub

J'espère que je suis assez claire sinon n'hésite à me poser plus de questions.

Merci encore
Manue

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Je ne vois toujours pas d'explication, je vais me lasser...

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Reprenons de zéro.

C'est un fichier qui regroupe toutes les références inventoriées dans le mois.
Moi je dois le traiter grâce à des macros.

Le premier onglet regroupera toutes les macros et leurs explications.
Dans le 2eme se trouve le fichier brut du mois que je reçois de l'informatique.

Avec ma 1ere macro pour le moment, j'arrive à copier l'onglet "fichier de base" dans l'onglet "Base valabs" puis à rajouter 3 colonnes :
- "valabs" qui a pour but de mettre le montant de l'écart (Colonne K) en valeur absolue
- "% valabs" qui m'informe de l'importance de l'écart en valeur absolue par rapport à la somme de tous les écarts en valeurs absolues du mois
- % valabs en cumulé qui est le % de la valeur absolue de la référence + celle des références précédentes.

Là, je souhaite en colonne B, calculer le nombre de références inventoriées dans le mois grâce à la formule NBVAL, sachant que le nombre de lignes varient en fonction des mois.

Suis-je plus claire ?

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Là, je souhaite en colonne B, ...
La colonne B de 'base valabs' est déjà remplie, tu confirmes que c'est celle-ci ?

...calculer le nombre de références inventoriées dans le mois grâce à la formule NBVAL, sachant que le nombre de lignes varient en fonction des mois.
Quelles références ? qui sont où ?
Je suppose qu'il faudra les extraire qcq part car tu as moins de références que de lignes (à de répéter le même nombre sur chaque référence identique ?)

Tu as le TCD (tableau croisé dynamique du menu données) qui fait ça très bien. En 10 clics tu as tes résultats...

eric

Réponse 6 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

L'onglet où il faut travailler est l'onglet "base valabs".
Les références se situent en colonne C donc erreur de ma part, c'est la colonne C où il faut faire la formule NBVAL

Je ne passe pas tout de suite par des tableaux croisés dynamique car j'ai des formules à créer (Colonnes L,M et N de l'onglet "base valabs").

Je te remets un lien avec l'onglet "base valabs" rempli au maximum pour que ce soit plus compréhensif pour toi.

https://www.cjoint.com/?3LFp5g4MQhK

Excuse de ma lenteur pour me faire comprendre mais c'est la ere fois que je fais appel à un forum.

Encore merci.
Manue

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Il y avait 2 questions.
Je ne vois pas la réponse à la 2nde :
Je suppose qu'il faudra les extraire qcq part car tu as moins de références que de lignes (à moins de répéter le même nombre sur chaque référence identique ?)
Où et comment veux-tu les résultats ?

On n'est pas devin et on lit pas ce que tu as en tête...

eric

Réponse 7 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Désolée mais je pensais avoir répondu en te disant que toutes les formules seront sur l'onglet "base valabs".

Et je n'ai pas besoin d'extraire les références pour le moment. Une références peut être inventoriée plusieurs fois. Là ce qui compte, c'est de savoir combien d'inventaires ont été effectué dans le mois.

Manue

Réponse 8 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Je souhaite avoir le nombre de références inventoriées sur la ligne qui suit la dernière référence (ici : en ligne 226; colonne C), dans l'onglet "base valabs".
Mais ne pas oublier que seul la colonne est fixe, le nombre de ligne de ligne varie

Merci

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

On y arrive....
Tu proposais nbval(), ce qui donne 224.
J'espère que ce n'est pas le nombre de ref différentes que tu voulais...

Dim derLig As Long
derLig = [C65536].End(xlUp).Row
Cells(derLig + 1, "C") = derLig - 1

eric

Réponse 9 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Non ce n'est pas du tout le nombre de références différentes mais le nombre de références totales.

Je regarde ta proposition.

Merci pour ton aide
Manue

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

J'ai supposé qu'il n'y avait pas de vides...
Et ça travaille sur la feuille active. Sinon il faut ajouter le nom de la feuille aux références.
eric

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

En effet, il ne peut pas y avoir de ligne sans références (donc vides).

J'ai essayé en modifiant un peu et ça fonctionne !!!!
Merci :)
Voilà ce que j'ai mis :

Dim DernierNoLig As Long
Dim NoLigDuCumul As Long

DernierNoLig = Range("A1").CurrentRegion.Rows.Count
NoLigDuCumul = DernierNoLig + 1

' Dans le cellule (Bx) insérer la formule NBVAL(Bx:B2)
Cells(NoLigDuCumul, 3) = DernierNoLig - 1

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Sans modifier ça fonctionnait aussi ;-)

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

oui ! :D . C'est pour pouvoir réutiliser mes variables dans mes prochaines formules :-)

Réponse 10 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Bonjour,

Tout d'abord bonne annnnéeeee !

Sinon, je me permet de refaire appel à toi, je beuggue sur une formule SUM. Je n'arrive pas à y intégrer une variable. Avec l'enregistreur de macro, la formule est la suivante :

ActiveCell.FormulaR1C1 = "=SUM(R[-224]C:R[-1]C)"

Le [-224] est variable, puisque c'est le nombre présent dans l'onglet "base valabs" sans compter celle des totaux.

Cela devrait donner une formule ressemblant à :

Cells(NoLigDuCumul, 12).FormulaR1C1 = "=SUM(R[-DernierNoLig]C:R[-1]C)"

De plus, j'ai un autre soucis avec une boucle qui doit calculer le % en valeur absolue (Colonne M de l'onglet "base valabs"). Je ne sais pas quel est le problème, la macro de me calcul rien. La boucle est la suivante :

Do While NoLig <= DernierNoLig
Cells(NoLig, 13).FormulaR1C1 = "=RC[-1]/Cells(DernierNoLig,12)"
NoLig = NoLig + 1
Loop

Je te joins mon fichier :

https://www.cjoint.com/?3AbmUCsdv5p

N'hésite à me poser plus de questions si besoin.

Merci d'avance si tu peux m'aider.
Manue

Réponse 11 / 13

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

Tous mes voeux de bonheur et de santé pour cette nouvvelle année également.

1) Je ne vais pas te le faire, je vais t'expliquer...
Lis tranquillement, ça parait long mais c'est parce que je détaille.

.FormulaR1C1 est ch... a utiliser : fonction en anglais et référence R1C1.

Utilise .formulalocal : tu copies ta formule à partir de la feuille et tu colles dans le code entre des " ".
De plus activer les cellules est un gros consommateur de ressources et ralenti terriblement le code. Ne le faire que lorsque c'est absolument nécessaire (cad presque jamais)
ex :

[D2].FormulaLocal = "=SOMME(A1:A3)"

ou si tu t'adresses à une feuille non active :

['Feuil1'!D2].FormulaLocal = "=SOMME(A1:A3)"

Si tu as une partie variable, remplacer le nombre par " & ta_variable & "
Il faut fermer la chaine ", insérer la variable et ouvrir la chaine " pour conserver la fin de chaine.
ex :

['Feuil1'!D2].FormulaLocal = "=SOMME(A1:A" & ta_variable & ")"

ce qui donnerait :

nb = 3   
['procédure'!D2].FormulaLocal = "=SOMME(A1:A" & nb & ")"

ou bien :

['procédure'!D2].FormulaLocal = "=SOMME(A" & nb & ":A25)"

Je te laisse essayer n'ayant pas envie de me prendre la tête avec les R1C1... ;-)

2) j'attend que tu aies converti ton .formulaR1C1 en .formulaLocal et je regarderai, si tu précises le nom et l'emplacement de la procédure en question...

eric

PS: n'oublie pas de mettre en résolu (en haut) lorsque le moment sera venu

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Je regarde avec FormulaLocal. J'utilise toujours FormulaR1C1 car c'est ce qu'utilise Excel par défaut quand on se sert de l'enregistreur de macro. De plus, en cours la prof nous fait utiliser cette version.

Mais je vais faire un essai ! en espérant que ce soit plus concluant.
Merci pour ta disponibilité ;-).
Je te tiens au courant.
Manue

Réponse 12 / 13

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

J'ai essayé comme tu dis, mais ça me met une erreur "Erreur de compilation ; Attendu fin d'instruction" et quand je clique sur "OK", ça me met en surbrillance les derniers caractères de la formule ")"

Voilà ce que j'ai fait :

Cells(NoLigDuCumul, 12).FormulaLocal = "=SOMME(L2:L"&DernierNoLig&")"4

De plus, je me sens obligée de nommer la cellule où je veux faire ma formule avant FormulaLocal. C'est ce que tu dis "être consommateur d'énergie" ?

Je ne veux pas que tu penses que je voudrais que tu me le fasse mais hier, j'ai passé pas mal de temps sur internet à chercher les différentes possibilités pour mettre une variable dans une fonction SOMME et quand j'essayé ça me mettait toujours ce type d'erreur.

J'avoue être embêtante :-) , c'est la première fois que j'essaie de créer un code.

Merci
Manue

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

La formule se situe dans ce fichier, en L226 de l'onglet "base valabs".
Le code se situe en VBA dans le module 1, Sub BaseValabs(), en dessous de l'avant dernière partie commençant par une phrase en vert.
J'espère avoir été claire :)

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

sépare les & avec des espaces et ça devrait le faire :
Cells(NoLigDuCumul, 12).FormulaLocal = "=SOMME(L2:L" & DernierNoLig & ")"
Le 4 à la fin je ne sais pas ce qu'il vient faire là...

De plus, je me sens obligée de nommer la cellule où je veux faire ma formule avant FormulaLocal. C'est ce que tu dis "être consommateur d'énergie" ?
Tu n'es pas obligée de nommer les cellules, bien que souvent ça éclairci les formules et le code.
Ce qui est consommateur de ressource c'est les .select suivi de activecell=...
La sélection est inutile.

Je ne veux pas que tu penses que je voudrais que tu me le fasse
Ce n'est pas que je ne veux pas le faire mais comme apparemment tu t'investis bien autant t'aider à progresser

eric

edit :
Le code se situe en VBA dans le module 1, Sub BaseValabs(), en dessous de l'avant dernière partie commençant par une phrase en vert.
J'ai des erreurs qui interviennent avant, je ne peux pas tester.
Mais je ne vois pas de réinitialisation du NoLig

eric

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Nikel !!! Les espaces, c'est magique :-)

Pour ma boucle, j'ai fait l'initialisation plus haut, en même temps que les autres.
J'ai mis :
Dim NoLig As Long
NoLig = 2

C'est bon ?
Moi par contre, ça fonctionne, j'ai pas d'erreur avant. C'est juste qu'il ne prend pas en compte ma boucle. Il ne fait rien.
Si toi tu as des erreurs, j'espère ne pas mettre trompée :(

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

remet NoLig = 2 juste avant pour voir

Si toi tu as des erreurs, j'espère ne pas mettre trompée :(
Je ne sais comment tu as prévu le fonctionnement (quoi lancer et dans quel ordre), les erreurs sont peut-être normales.

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

J'ai suivi ton conseil. C'est déjà mieux mais ça m'indique la formule dans les cellules de la colonne M, mais pas le résultat. Peux-tu me dire où je dois chercher ?

Sinon si tu déclenches les macros par les boutons présent dans la feuille "procédure" du fichier, il n'y a que la 1ere macro "base valabs" qui fonctionne.

Réponse 13 / 13

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Ben ouais, c'est comme si on avait rien foutu aujourd'hui..
Je confirme, tu as perdu toutes les modifs...
Régulièrement enregistre en incrémentant le n° de version.
A faire systématiquement avant chaque changement important, et après toute version stable déboguée.
Tu l'appelles blabla v1.xls, puis blabla v2. xls etc

Bon, j'ai repris presque tout pour que ça tourne.
Ce que tu voulais et ce qui ne me plaisait pas, je te laisse contrôler.
https://www.cjoint.com/?BAbwfAYyrEt

Si ça te parait bon continue tes modif sur une version v2 !!!!

manueHN Messages postés 35 Date d'inscription Statut Membre Dernière intervention

Tu me sauve la vie !!!! ça fonctionne :-)
Je crée une version V2, immédiatement ! et me remets à la tâche.
Je ne t'embête pas plus, tu en as déjà fait beaucoup pour aujourd'hui !
Et j'espère ne pas avoir à te redemander de l'aide ;) ... peut être 1 fois ou 2 ^-^
Encore merci !
Manue

Répondre Posez votre question

Votre réponse

Discussions similaires

activer un classeur en vba excel

ch'duj -
thomas -

2 réponses

envoyer feuille excel en pdf par mail

gnioler -
gnioler -

4 réponses

recuperer nom fichier excel variable vba

gnioler -
gnioler -

2 réponses

Macro, excel, rechercher une valeur

clemdore -
michel_m -

5 réponses

envoyer excel en pdf via outlook vba

gnioler -
Nore -

39 réponses

VBA recherchev** dans feuilles autre classeur

nauard -
lermite222 -

9 réponses

Devenez membre en quelques clics

Connectez-vous simplement avec ceux qui partagent vos intérêts
Suivez vos discussions facilement et obtenez plus de réponses
Mettez en avant votre expertise et aidez les autres membres
Profitez de nombreuses fonctionnalités supplémentaires en vous inscrivant

S'inscrire