Formule NBVAL sur prog excel VBA Résolu/Fermé

Question

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. 

Configuration: Windows 7 / Safari 535.7

eriiic · Answer

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

manueHN · Answer

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

manueHN · Answer

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

manueHN · Answer

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

manueHN · Answer

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 ?

manueHN · Answer

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

manueHN · Answer

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

manueHN · Answer

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

manueHN · Answer

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

manueHN · Answer

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

eriiic · Answer

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 · Answer

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

eriiic · Answer

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 !!!!

Formule NBVAL sur prog excel VBA

13 réponses

Discussions similaires