Discussion :

[Arthurotop]

Bonjour ! Une nouvelle journée, un nouveau problème !

Rentrons dans le vif du sujet, je ne trouve pas la solution sur internet alors je me tourne vers vous

Ma situation
Je travail dans un festival et j'ai un fichier avec une liste d'email. Il faut que je sache le nombre de place à envoyer par email, pour cela j'ai une formule (=NB.SI($B$2:$B$74;B2)).
Avec B2 la ligne de la première adresse mail et B74 la dernière ligne avec une adresse mail (qui est donc variable en fonction du fichier que l'on m'envoie)
Normalement je modifie manuellement la valeur de la dernière ligne mais j'aimerai automatiser cette partie.

Mon problème
En gros, avec une formule en VBA je récupère le nombre de ligne non vide d'une colonne de mon fichier (351)

Je rentre le résultat de la formule dans une TextBox et j'aimerai appliquer la formule (cf.=NB.SI($B$2:$B$74;B2)) sur une plage.

Ce que j'essaie de faire c'est d'appliquer sur la plage "C2:Colonne C + valeur du TextBox" = NB.SI($B$2:$B$valeur du TextBox;B2) afin que mon code soit utilisable peu importe la longueur du fichier à traiter.

Seulement cela ne fonctionne pas (j'ai compris que ma syntaxe est très mauvaise mais je ne trouve aucune trace de quelqu'un qui aurait voulu faire pareil que moi sur internet)

J'ai aussi noté comme problème le fait que je ne peux pas utiliser le ";" dans la formule NB.SI($B$2:$B$74;B2).

Merci de votre attention

[mat955]

Bonjour,

Inutile de passer par VBA.
Dans un premier temps, tu peux compter le nombre de cellules vides ou non vides avec la fonction NB.SI
Et après pour la rendre dynamique, tu peux utiliser la fonction DECALER

[Arthurotop]

Je sais que VBA n'est pas nécessaire pour ce problème, j'ai du le faire dans le rush et je m'en suis sorti sans utiliser VBA
Mais dans le cadre de mes études, je dois utiliser VBA et j'aimerai laisser un moyen plus simple à l'entreprise d'effectuer cette tache, voici donc pourquoi j'essaie de le faire de cette manière, afin qu'ils aient un Userform qui s'ouvre, afin d'effectuer toute la démarche automatiquement (sur des fichiers de plusieurs milliers de lignes, c'est très long)

[mat955]

... j'aimerai laisser un moyen plus simple à l'entreprise d'effectuer cette tache...

Je n'ai peut-être pas saisie toute la complexité du problème, mais quoi de plus simple qu'une formule ?
Je veux bien un peu plus de matière pour mieux comprendre, parce que là j'ai l'impression qu'on essaye de construire un viaduc pour traverser une rivière, là où deux planches suffiraient

[Arthurotop]

Haha j'aime bien l'image et c'est totalement le cas, fondamentalement j'étais dans la situation : "colonne 1 = nom et prénom" ; "colonne 2 = email" et je crée une "colonne 3 = quantity" afin de savoir combien de place il faut envoyer aux festivaliers.

Partie 1
Dans cette colonne 3 j'applique la formule =NB.SI($B$2:$B$74;B2) en modifiant "$B$74" en remplaçant 74 par le nombre de client (spoiler, c'est à chaque fois 1 la quantité qu'il faut envoyer)

Partie 2
Une fois cela fait, je dois séparer le nom et le prénom (Données => convertir => délimiteur => etc) puis regrouper tous les seconds prénom dans une case afin d'avoir une colonne nom et une colonne prénom (grâce à la fonction concat(A1; " "; A2; " "; etc))

Exemple : Arthur de la Vilette => Arthur ; De ; La ; Vilette => Arthur ; De la vilette (afin d'avoir uniquement deux colonnes pour nom et prénom)

La le travail est terminé (on est d'accord, c'est hyper simple). Mais dans le cadre de mes études pour remplir mon rapport de stage, j'aimerai automatisé ce travail donc :

Partie 1- Compter automatiquement le nombre de client (ligne) et le rentrer dans une textbox (afin de pouvoir vérifier que le compte est bon)
Partie 2- Utiliser le contenu de la textbox dans la formule qui m'est imposé (pour que ce soit dynamique et que peu importe le fichier, cela se fasse automatiquement) sans tenir compte du fait que la formule n'est pas acceptée par VBA, il doit y avoir des équivalents pour chaque caractère non accepté par VBA (comme le ; avant le B2 à la fin de la formule)
Partie 3- Séparer les noms/prénoms et les faires tenir dans deux colonnes

Actuellement je suis bloqué à la partie 2

[mat955]

Bon alors si on part sur une construction de viaduc, quand tu as besoin d'écrire une formule en VBA, le plus simple est d'écrire ta formule dans Excel, puis de passer par l'enregistreur de macro.

Ce qui te donne le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("C2").FormulaR1C1 = "=COUNTIF(R2C2:R74C2,RC[-1])"

Et après, tu n'as plus qu'à l'adapter avec ta textbox, ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("C2").FormulaR1C1 = "=COUNTIF(R2C2:R" & TextBox1.Value & "C2,RC[-1])"

Attention, si la textbox ne contient pas un nombre, le pont s'écroule !

[Arthurotop]

Me revoilà ! Je vais tester ça de ce pas et je reviens te dire si le viaduc à l'air de fonctionner

[Philippe Tulliez]

Bonjour,
Pour compter le nombre de cellules remplies, j'utiliserais plutôt NBVAL dont c'est le rôle et en utilisant un tableau structuré, il suffit d'utiliser l'option Nombre dans la ligne des totaux pour l'obtenir
Pour séparer le nom du prénom privilégiez POWER QUERY
Le VBA pour moi, n'a pas sa place ici

[Arthurotop]

Cela fonctionne !! Du moins sur une case :'(
J'essaie donc de faire en sorte que la range

Range("C2").FormulaR1C1

aille de C2 à la cellule C[textbox1.value]

J'ai essayé de le faire en enregistrant une macro mais ça me donne range(C2:C351) ce qui fonctionne mais que dans ce cas précis

Philippe, j'ai déjà expliqué plus haut dans la discussion que malgré le fait que ça ne soit pas nécessaire, c'est quand même ce que j'essaie de faire afin de remplir mon rapport de stage et de laisser quelque chose de fonctionnel à l'entreprise qui m'a accueilli pendant 3 mois

[Philippe Tulliez]

Bonjour,

Philippe, j'ai déjà expliqué plus haut dans la discussion que malgré le fait que ça ne soit pas nécessaire, c'est quand même ce que j'essaie de faire afin de remplir mon rapport de stage et de laisser quelque chose de fonctionnel à l'entreprise qui m'a accueilli pendant 3 mois

Et bien utilisez les tableaux structurés, vous laisserez une meilleure image de vos connaissances que d'utiliser une vieille méthode mais évidemment vous êtes libre de vos actes.

[Arthurotop]

Est-ce que les tableaux structurés fonctionne en VBA ? C'est le point principal de mon problème, quoi que je fasse, il faut le faire en VBA :/

[Philippe Tulliez]

Bonjour,

Est-ce que les tableaux structurés fonctionne en VBA ?

Bien entendu.
Tout ce que l'on peut faire manuellement dans Excel peut être automatisé en VBA.
Un tableau structuré est un objet comme les cellules, feuilles, classeurs, tableaux croisé dynamique, etc. soit un ListObject

Pour en savoir plus, je vous conseille la lecture de ce tutoriel de Pierre Fauconnier Excel : Les tables de données en VBA

[Arthurotop]

J'ai commencé à lire en diagonale afin de trouver une solution mais je n'y arrive pas, j'essaie donc d'appliquer la formule sur la pllage allant de C2 à C[valeur de la textbox] mais je n'y arrive pas, j'ai tester plusieurs manière mais rien ne fonctionne :'(

[Philippe Tulliez]

Bonjour,
Une formule dans VBA est une chaîne de caractères donc il s'agit d'utiliser des concaténations ou des remplacements si cette chaîne est constituée de plusieurs sources dont la valeur contenue dans un TextBox.
J'ai écrit deux billets sur ce sujet

• Ecrire une formule dans Excel à l'aide d'une procédure VBA
• Comment écrire une formule plus complexe dans une cellule ?

[Arthurotop]

J'ai lu tes deux billets et ils expliquent comment transformer la partie NB.SI en COUNTIF pour que ça fonctionne en VBA, cependant la partie que je cherche à modifier c'est le range(H2:H101) en range(H2:HValeur de la textbox1)

[Arthurotop]

J'ai trouvé la solution, après de multiple tentative, c'est la forme

Range("C2:C" & TextBox1.Value)

qui a fonctionnée

Je continue mon viaduc et je vous tiens au courant

[Arthurotop]

Le viaduc est fini, il me reste qu'une seule tâche à essayer de résoudre :
Chaque fichier que je dois traiter peut faire entre 100 et 1500 lignes, hors le site sur lequel il faut envoyer le fichier n'accepte que des fichiers de maximum 250 lignes, je vais donc chercher sur internet s'il est possible d'automatiser le découpage d'un fichier de + de 250 lignes en plusieurs fichiers de 249 lignes. Je ne suis pas sur que ce soit possible (ça serait très dangereux que excel puisse effectuer des tâches qui consiste à créer des fichiers sur un ordinateur (en dehors du classeur actif)..

En tout cas merci beaucoup messieurs ! Je mets le sujet en résolu

[Philippe Tulliez]

Bonjour,
Pourquoi ne serait-ce pas possible ?
C'est un simple Couper/Coller sur un nouveau classeur

[Arthurotop]

L'objectif est de ne pas avoir à ouvrir manuellement un nouveau classeur, excel peut-il via une macro ouvrir autant de classeurs qu'il faut pour diviser le fichier originel en plusieurs fichiers de 249 lignes ? Ou est-on obligé d'ouvrir de nouveaux classeurs manuellement ? (pour un fichier de 2000 lignes, il faudrait ouvrir 8 nouveaux classeurs)

[Philippe Tulliez]

Bonjour,
Comme je l'ai déjà écrit dans le fil #12, "Tout ce que l'on peut faire manuellement dans Excel peut être automatisé en VBA."

Il suffit donc d'effectuer une boucle sur la plage source et à l'intérieur de la boucle

• copier ou couper 250 lignes et les coller vers une plage cible d'une feuille dédiée dans le même classeur puis copier la feuille vers un nouveau classeur ou d'ouvrir un nouveau classeur et coller les lignes dans la première feuille
• Faire un Sauvez-sous de ce nouveau classeur
• Le fermer