Formule NBVAL sur prog excel VBA
Résolu
manueHN
Messages postés
36
Statut
Membre
-
manueHN Messages postés 36 Statut Membre -
manueHN Messages postés 36 Statut Membre -
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.
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.
13 réponses
Résumé de la discussion
Une programmation Excel VBA vise à compter mensuellement les lignes d’un fichier dont le nombre varie, et une erreur survient sur une ligne de formule écrite en notation R1C1. Le souci pratique réside dans la syntaxe de la ligne Cells(NoLigDuCumul, 2).FormulaR1C1 = "=CountA (R[1]C2:R[-1]C2)", où l’espace et l’orthographe de CountA posent problème; utiliser COUNTA sans espace résout l’erreur. D'autres échanges suggèrent d'initialiser correctement les variables (par exemple Dim NoLig As Long et NoLig = 2) et de vérifier la condition d’arrêt de Do While pour éviter une boucle infinie. Plusieurs contributeurs recommandent une gestion de versions et l’ajustement des formats (par exemple pour les colonnes VALABS et % VALABS) afin d’éviter des incohérences lors des tris ou des calculs.
-
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 -
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 -
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 -
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 -
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question -
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 ?-
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
-
-
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-
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
-
-
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 -
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 -
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-
-
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 -
-
-
-
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 -
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-
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
-
-
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-
-
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 -
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 :( -
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. -
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.
-
-
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 !!!!
