Formule NBVAL sur prog excel VBA [Résolu]

Réponse 2 / 13

manueHN Messages postés 35 Date d'inscription samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 12:50

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
31 déc. 2011 à 13:29

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 13:36

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
31 déc. 2011 à 14:02

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

Réponse 4 / 13

manueHN Messages postés 35 Date d'inscription samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 14:11

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
31 déc. 2011 à 14:20

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 14:37

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 31/12/2011 à 15:09

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 15:57

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 31/12/2011 à 18:08

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 18:12

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 18:15

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
31 déc. 2011 à 18:33

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 18:36

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 31/12/2011 à 18:40

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 18:44

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
31 déc. 2011 à 18:54

Sans modifier ça fonctionnait aussi ;-)

manueHN Messages postés 35 Date d'inscription samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
31 déc. 2011 à 18:55

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 12:47

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 1/01/2012 à 13:36

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 13:45

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 14:16

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 14:19

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 1/01/2012 à 14:43

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 15:06

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
Modifié par eriiic le 1/01/2012 à 16:08

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 16:35

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 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 212
1 janv. 2012 à 22:10

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 samedi 31 décembre 2011 Statut Membre Dernière intervention 24 avril 2012
1 janv. 2012 à 22:15

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

Formule NBVAL sur prog excel VBA

13 réponses

Discussions similaires

Newsletters