Formule longue et qui se répète

gaetan2812 · 26 Mai 2014

Bonjour,

Je connais ce forum depuis quelques temps, je ne m'étais pas encore inscrit car j'arrivais toujours à trouver un cas similaire au mien, mais là je bloque.

Je vous explique mon problème. Dans mon onglet 3, je dois vérifier par rapport au nom sélectionné, quelles cases sont cochées dans l'onglet 1, et ensuite cela vérifie s'il y a au moins un "oui" dans l'onglet 2 pour le paramètre (collège, lycée ...) choisi.

Mon problème est donc la longueur de la formule de C5 à C7 qui dépend du nombre de "collège 1, primaire 1, lycée 1 ...". Là on peut voir dans l'onglet 1 qu'il y en a 6 et on doit donc vérifié pour chaque type qu'il y a une X.

La formule est assez longue là, mais le truc c'est qu'en réalité, je peux monter à une très grande quantité de "collège 1, primaire 1, lycée 1 ..." ce qui fait qu'à un moment je dépasse les 8192 caractères maximum d'une formule.

A cela je peux rajouter le fait que l'onglet 2 est parfois accompagné d'un autre onglet genre 2 bis où on pourrait avoir par exemple "collège 3, primaire 3, lycée 3..."

Au final ça multiplie le nombre de "Onglet 2"! ... etc dans ma formule finale, qui accroit donc le nombre de caractères.

Auriez-vous un moyen pour que ça passe tout seul des cases C3 à H3 de l'onglet 1 et donc éviter la répétion du bout de formule (SI(RECHERCHEV($B$2;'Onglet 1'!A4:H8;4;FAUX)="X";SI(NB.SI.ENS('Onglet 2'!A$1:A$100;"Primaire 2";'Onglet 2'!B$1:B$100;"Chimie";'Onglet 2'!C$1:C$100;"X")>0;"X";"");""))="X" ?

J'ai écris en bleu la partie "variable" de la formule.

Je vous remercie par avance.

gaetan2812 · 2 Juin 2014

Re : Formule longue et qui se répète

J'ai malheureusement toujours le même problème avec Pers.2 qui a "Y N N" au lieu d'avoir "Y Y Y". Je ne vois pas d'où ça peut venir

vgendron · 2 Juin 2014

Re : Formule longue et qui se répète

ca vient du fait que la macro ne prend pas en charge les onglets bis..

1) Créer une liste des onglets à scruter: Onglet 2, 2bis...
2) modifier le code pour aller chercher les matières dans tous les onglets

gaetan2812 · 2 Juin 2014

Re : Formule longue et qui se répète

J'ai voulu faire un teste avec l'ancien fichier Exemple Tableau (2).xlsm, le dernier que tu as envoyé avant celui-là où il n'y avait qu'un onglet 2. J'ai remplacer le code par le nouveau, et j'ai toujours l'erreur pour Pers. 2.

Sur le fichier que tu m'as envoyé, Pers.2 n'a plus le problème ?

vgendron · 2 Juin 2014

Re : Formule longue et qui se répète

utilise le dernier fichier, car la zone nommée "ListeEcoles" se met à jour automatiquement:
la macro fera la meme chose, meme avec l'onglet 2bis, puisqu'elle ne va pas le regarder..

pour bien voir ce qui va. ou pas. lance la macro en mode pas à pas: Touche F8

d'abord, tu ouvres l'editeur VBA: Alt + F11
à gauche dans l'explorateur, tu cliques sur le module 1 qui contient la macro
tu cliques Dans la macro
puis à chaque fois que tu appuies sur la touche F8, le code avance d'une ligne

comme ca, tu peux suivre exactement ce que la macro fait

vgendron · 2 Juin 2014

Re : Formule longue et qui se répète

Bon.. avec CE fichier Rev4

1) comme l'onglet 2bis n'est pas pris en compte par la macro, j'ai recopié les écoles DANS l'onglet 2
2) pour la personne 2, je lance la macro, et ca me donne Y N Y: et c'est bien ce que j'attend quand je le fais manuellement
pour la personne 2: il y a 3 écoles: Primaire 2 - Lycée 1 - Collège 3

d'après l'onglet 2:
Lycée1: PAS de matière donc 3 N
Primaire 2: Pas de matière donc 3N
collège 3: il n y a que Chimie et Math. donc Y N Y..

du coup.. ca m'a donné une idée pour les onglets;
plutot que de modifier la macro pour la faire boucler sur tous les onglets (j'ai commencé. mais. ca casse tout)
tu commences par regrouper tous tes onglets dans un onglet unique

gaetan2812 · 2 Juin 2014

Re : Formule longue et qui se répète

C'est impressionnant car si jamais je supprimer la croix Oui dans chimie du collège 3, j'ai quand même un Y qui sort à la fin. Je comprends vraiment pas, ça marche bien pour le reste mais pas pour lui.

Même quand je mets toutes les écoles de Pers.2 avec 0 matières, il y a toujours un Y en face de Chimie.

Alors que pour Pers. 4 qui n'a rien non plus, j'ai bien 3 N. Mais si je rajoute une croix pour qu'il ait une école de plus, mais sans matière, il sort une croix en face de chimie. En fait, dès que le nombre de croix est au moins égal à 3 dans l'onglet 1, le résultat est faux dans l'onglet 3.

vgendron · 2 Juin 2014

Re : Formule longue et qui se répète

trouvé:

erreur d'indice..
modifie la ligne suivante

Code:

 'forcément, le .find va trouver la PREMIERE occurence de cette école
            LigneEcole = d.Row

à remplacer par

Code:

 'forcément, le .find va trouver la PREMIERE occurence de cette école
            LigneEcole = d.Row - 1

Manque le -1

vgendron · 2 Juin 2014

Re : Formule longue et qui se répète

Rev5:
j'ai rajouté une macro (bouton Recap Onglet) à ne lancer qu'une fois la première fois

ensuite, macro globale pour remplir les Y ou N.

la. j'ai pas tout compris. pour que ca marche, il faut que je remette la ligne initiale. sans le -1
LigneEcole = d.Row

gaetan2812 · 2 Juin 2014

Re : Formule longue et qui se répète

OK merci beaucoup.

J'ai vu tes deux messages en même temps

Code:

Sub cherche()

'on commence par effacer les matières
Range(Cells(5, 2), Cells(5, 2).End(xlDown)).Clear

'récupère le nom de l'élève concerné
NomEleve = Sheets("Onglet 3").Range("B2")

'rercherche de cet élève dans l'onglet 1 et on récupère la ligne ou il est situé
Set c = Sheets("Onglet 1").Range("TabOng1").Find(NomEleve)
If Not c Is Nothing Then
    LigneEleve = c.Row
End If

'on compte le nombre d'écoles dans la zone nommée ListEcoles
'Note: il faudra définir cette zone dynamiquement avec decaler....
'voir le gestionaire de noms
'ListeEcoles=DECALER('Onglet 1'!$C$3;0;0;1;NBVAL('Onglet 1'!$3:$3)-4)
'permettra ansi de rajouter autant d'écoles que souhaitées, la macro fonctionnera toujours

NbEcoles = Sheets("Onglet 1").Range("ListEcoles").Columns.Count

'pour chaque école, on regarde si l'élève est concerné: présence d'une croix
For j = 3 To 2 + NbEcoles
    If Sheets("onglet 1").Cells(LigneEleve, j) = "X" Then
        'si concerné, alors on note cette école pour ensuite aller voir les matières dans l'onglet2
        EcoleEnCours = Sheets("onglet 1").Cells(3, j)
        'recherche de la position de l'école dans l'onglet 2
        Set d = Sheets("Onglet 2").Range("tabOng2").Columns(1).Find(EcoleEnCours)
        If Not d Is Nothing Then
            'forcément, le .find va trouver la PREMIERE occurence de cette école
            LigneEcole = d.Row - 1
            
            For k = 1 To 3 '3=Nb Matières
                'on récupère la matière k à coté de l'école
                MatEnCours = Sheets("Onglet 2").Cells(LigneEcole + k - 1, 2)
                If IsEmpty(Sheets("Onglet 2").Cells(LigneEcole + k - 1, 3)) Then
                    MatValidée = "N"
                Else: MatValidée = "Y"
                End If
                'on cherche la position de la matière dans l'onglet 3
                Set e = Sheets("Onglet 3").Range("A1:A10").Find(MatEnCours)
                If Not e Is Nothing Then
                    LigToCol = e.Row
                    'condition OU: si il y a déjà un OUI, on ne fait rien
                    If (Sheets("Onglet 3").Cells(LigToCol, 2) = "N") Or IsEmpty((Sheets("Onglet 3").Cells(LigToCol, 2))) Then
                        Sheets("Onglet 3").Cells(LigToCol, 2) = MatValidée
                    End If
                End If
            Next k
        End If
    End If
Next j
End Sub

Donc j'ai testé le -1 pour voir et ça marche impec. Comment est-ce que cela se fait ? Comment le -1 à tout arrangé ?

Et sinon pour ton dernier mess avec la création du bouton récap, c'est sympa, mais comme je l'ai dit plus haut, j'ai trop de données pour créer un ongle comme cela. Par contre je vais modifier la macro pour l'adapter à chaque onglet et créer des cases intermédiaires.

JE n'ai donc plus qu'à l'adapter à mon fichier en espérant que tout marche bien. Je te remercie grandement.

vgendron · 3 Juin 2014

Re : Formule longue et qui se répète

le -1 "arrange" parce que, la recherche se fait dans une zone nommée "ListeEcoles"

et il y avait une différence entre la position de l'école dans la liste et la position de l'école dans la feuille excel
exemple:
A5: Ecole1
A6: Ecole2
A7: Ecole3

Ecole 2 est en position 2 dans la liste MAIS est en ligne 6 d'excel

Formule longue et qui se répète

gaetan2812

XLDnaute Junior

Pièces jointes

gaetan2812

XLDnaute Junior

vgendron

XLDnaute Barbatruc

gaetan2812

XLDnaute Junior

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Pièces jointes

gaetan2812

XLDnaute Junior

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Pièces jointes

gaetan2812

XLDnaute Junior

vgendron

XLDnaute Barbatruc

Discussions similaires