Plage variable - index equiv VS boucle VBA

Le 03/04/2015 à 16:15

bonjour

j'ai un classement avec une plage variable

je souhaite reporter ce classement ( en page p1 du fichier ) et le ranger dans un tableau d'une autre page ( page affiche )

de mon classeur

=INDEX('P1'!B7:H10;EQUIV(C11;'P1'!H7:H10;0);1)

la formule excel suivante convient bien mais le souci c'est qu'elle ne prend pas en compte ma plage varaible

et vu que j'efface la page "affiche" a chaque ouverture du dossier je dois utiliser vba

1 ) etape defnir la plage de donnee

db = Columns("B").Find("nombre", lookat:=xlWhole).Row

fin = Columns("A").Find("stop", lookat:=xlWhole).Row

2) ensuite on cree une boucle for next qui ne travaille que dans la plage

la je sais pas trop ?

comment traduire >> ndex equiv en vba ?:

fichier en piece jointe plus explicite que mes explications

23total.xlsm (27.94 Ko)
Le 03/04/2015 à 16:48

Bonjour,

une proposition

Sub Macro()
Dim db, fin, lg, x
' db  debut - fd fin de plage , lg longeur de plage , x varaible

Set wsp1 = Sheets("p1")
Set wsa = Sheets("affiche")

 db = wsp1.Range("B:B").Find("nombre", lookat:=xlWhole).Row + 1
 fin = wsp1.Range("C:C").Find("stop", lookat:=xlWhole).Row - 1
For x = db To fin on parcourt la liste des noms de P1
wsa.Cells(wsp1.Cells(x, "H") + 10, "D") = wsp1.Cells(x, "C") 'copie le nom de p1 dans affiche
wsa.cells(wsp1.cells(x,"H")+10,"C")=wsp1.cells(x,"B") ' copie le nombre
wsa.cells(wsp1.cells(x,"H")+10,"B")=wsp1.cells(x,"H") ' copie la position au classement
Next x

End Sub
Le 03/04/2015 à 16:57

Ce n'est pas la peine de traduire en VBA même si ta plage est variable. Tu peux définir un nom de plage dynamique, et là ce sera plus simple !

De quelle plage parles-tu ? B7:H10 ?

Elle est variable en largeur ? en hauteur ?

Le 03/04/2015 à 17:11

oui il s'agit de la plage H7 a H10

et oui variable en hauteur

et je veux traduire en vba car sinon je devrais utiliser

formulalocal = (index ....equiv..) et autofill ensuite

car j'efface l'ensemble de la page ainsi que la page de reference

ici il manque la parenthese

wsa.cells(ws1.cells(x,"H")+10,"B"=

par contre petit souci une erreur au niveau de la plage 'erreur 1004 "

je vais essayer de comprendre le pourquoi

Le 03/04/2015 à 17:14

Bonjour le fil, bonjour le forum,

Une autre proposition :

Sub Macro1()
Dim OP As Worksheet 'déclare la variable OP (Onget P1)
Dim OA As Worksheet 'déclare la variable OA (Onget Affiche)
Dim CD As Range 'déclare la variable CD (Cellule de Départ)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim TF() As Variant 'déclare la variable TF (Tableau Final)

Set OP = Sheets("P1") 'définit l'onglet P1
Set OA = Sheets("Affiche") 'définit l'onglet OA
Set CD = OP.Columns("B").Find("nombre", lookat:=xlWhole) 'définit la cellule de départ CD
If Not CD Is Nothing Then 'condition : si il existe au moins une occurrence trouvée
    TC = CD.CurrentRegion 'définit le tableau de cellules TC
Else 'sinon
    MsgBox "la valeur " & Chr(34) & "nombre" & Chr(34) & " n'a pas été trouvée !" 'message
    Exit Sub 'sort de la procédure
End If 'fin de la condition
ReDim TF(1 To UBound(TC, 1), 1 To 3) 'redimentionne le tableau final TF (autant de lignes que TC et 3 colonne)
TF(1, 1) = "Classement" 'définit la valeur ligne 1 colonne 1 de TF
TF(1, 2) = "Nombre" 'définit la valeur ligne 1 colonne 2 de TF
TF(1, 3) = "Prénom" 'définit la valeur ligne 1 colonne 3 de TF
For J = 1 To UBound(TC, 1) - 1 'boucle 1 : de 1 au nombre de ligne de TC moins une
    For I = 2 To UBound(TC, 1) 'boucle 2 : de 2 au nombre de lignes de TC
        If TC(I, 7) = J Then 'condition : si la valeur ligne I colonne 7 de TC est égale à J
            TF(J + 1, 1) = J 'renvoie dans la ligne J+1 ,colonne 1 de TF le classement (=J)
            TF(J + 1, 2) = TC(I, 1) 'renvoie dans la ligne J+1 ,colonne 2 de TF le nombre (=TC(I,1))
            TF(J + 1, 3) = TC(I, 2) 'renvoie dans la ligne J+1 ,colonne 3 de TF l'élève (=TC(I,2))
            Exit For 'sort de la boucle 2
        End If 'fin de la condition
    Next I 'prochaine ligne de la boujcle 2
Next J 'prochaine ligne de la boucle 1
OA.Range("B10").CurrentRegion.ClearContents 'efface les anciennes données
'renvoie le tableau TF dans la cellule B10 (redimensionnée) de l'onglet OA
OA.Range("B10").Resize(UBound(TF, 1), UBound(TF, 2)).Value = TF
End Sub

Attention ! dans ton fichier exemple, dans la cellule B7 de l'onglet P1 il y a un espace après nombre "nombre ". À corriger... Il faut aussi effacer le Stop qui n'a plus d'utilité dans le code proposé.

Le 03/04/2015 à 17:42

Merci a tous pour vos réponses

effectivement il y a un espace en trop dans la case b7 !! corriger dans le nouveau fichier !

h2so4 je n'arrive pas a faire fonctionner le code imcompatabilite de type

dommage il avait l'air court et simple

je savais pas que l'on pouvait faire ca dans une boucle ( je vais reprendre l'idee pour une autre parti de mon code )

wsa .cell .....= ws

ThauThème merci pour les 2 propositions ainsi que les explications a a cote ( tres pratique pour moi qui ne maitrise pas autant vba que vous )

j'ai pas encore analyser le code mais j'ai tester

c'est pas mal mais non cela ne convient pas

car parfois je risque d'avoir une ligne vide entre plusieurs valeurs

( bah oui histoire de vous embêter un peu )

c'est pour cela que je cherche a définir un début et une fin de plage pour etre sur de tt prendre !!

( avec les mots ( nombre et stop ) )

le code de h2so4 fonctionne

il y a du avoir des 1 a la place de l

Le 03/04/2015 à 21:01

Bonsoir le fil, bonsoir le forum,

Au cas où... Le code modifié avec gestion des lignes vide :

Sub Macro1()
Dim OP As Worksheet 'déclare la variable OP (Onget P1)
Dim OA As Worksheet 'déclare la variable OA (Onget Affiche)
Dim CD As Range 'déclare la variable LD (Cellule de Départ)
Dim CF As Integer 'déclare la variable CF (Colonne de fin)
Dim LD As Integer 'déclare la variable LD (Ligne de Départ)
Dim LF As Integer 'déclare la variable LF (Ligne de Fin)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim TF() As Variant 'déclare la variable TF (Tableau Final)

Set OP = Sheets("P1") 'définit l'onglet P1
Set OA = Sheets("Affiche") 'définit l'onglet OA
Set CD = OP.Cells.Find("nombre", lookat:=xlWhole) 'définit la cellule de départ CD
If Not CD Is Nothing Then 'condition : si il existe au moins une occurrence trouvée
    LD = CD.Row 'définit la ligne de départ
    CF = OP.Cells(LD, Application.Columns.Count).End(xlToLeft).Column 'définit la colonne de fin CF
    LF = OP.Cells(Application.Rows.Count, CD.Column).End(xlUp).Row 'définit la ligne de fin LF
    TC = OP.Range(CD, OP.Cells(LF, CF))
Else 'sinon
    MsgBox "la valeur " & Chr(34) & "nombre" & Chr(34) & " n'a pas été trouvée !" 'message
    Exit Sub 'sort de la procédure
End If 'fin de la condition
ReDim TF(1 To UBound(TC, 1), 1 To 3) 'redimentionne le tableau final TF (autant de lignes que TC et 3 colonne)
TF(1, 1) = "Classement" 'définit la valeur ligne 1 colonne 1 de TF
TF(1, 2) = "Nombre" 'définit la valeur ligne 1 colonne 2 de TF
TF(1, 3) = "Prénom" 'définit la valeur ligne 1 colonne 3 de TF
For J = 1 To UBound(TC, 1) - 1 'boucle 1 : de 1 au nombre de ligne de TC moins une
    For I = 2 To UBound(TC, 1) 'boucle 2 : de 2 au nombre de lignes de TC
        If TC(I, 7) = J Then 'condition : si la valeur ligne I colonne 7 de TC est égale à J
            TF(J + 1, 1) = J 'renvoie dans la ligne J+1 ,colonne 1 de TF le classement (=J)
            TF(J + 1, 2) = TC(I, 1) 'renvoie dans la ligne J+1 ,colonne 2 de TF le nombre (=TC(I,1))
            TF(J + 1, 3) = TC(I, 2) 'renvoie dans la ligne J+1 ,colonne 3 de TF l'élève (=TC(I,2))
            Exit For 'sort de la boucle 2
        End If 'fin de la condition
    Next I 'prochaine ligne de la boujcle 2
Next J 'prochaine ligne de la boucle 1
OA.Range("B10").CurrentRegion.ClearContents 'efface les anciennes données
'renvoie le tableau TF dans la cellule B10 (redimensionnée) de l'onglet OA
OA.Range("B10").Resize(UBound(TF, 1), UBound(TF, 2)).Value = TF
End Sub
Le 03/04/2015 à 21:48

Bonsoir,

ici il manque la parenthese

wsa.cells(ws1.cells(x,"H")+10,"B"=

effectivement, il manquait une parenthèse, j'ai corrigé le code dans ma réponse initiale.

Le 03/04/2015 à 21:51

bon finalement ça se complique ma petite histoire .....

d'abord Bravo pour vos différents code , on en joue pas dans la même cour ...

utiliser une plage variable ( avec decaler et nbval ( c'est exclu car il y a des lignes vides parfois ))

a moins d'arriver a définir autrement la plage variable ?

avec les mots rechercher comme en vba ?? mais cela ne résout pas tt ( ref#)

pour le vba

histoire d'en rajouter une couche

j'ai parfois des commentaires a la place du classement !

et lors de ma creation du fichier de travail , j'ai intervertis 2 colonnes

( donc en fait les 3 macro fonctionne mais des qu'il y a du texte ou une ligne vide c'est fini !

du coup j'ai revu mon fichier de travail et voici une version corrigé

10total1.xlsm (33.71 Ko)
Le 03/04/2015 à 22:15

Bonsoir,

J'ai adapté la macro et remplacé les "ws1" par des "wsp", ce qui prête moins à confusion.

résultat dans le fichier joint

22total1.xlsm (32.48 Ko)
Le 03/04/2015 à 23:13

Cela semble bien fonctionner demain je adapte ca devrais le faire ... en attendant c'est résolu ....

Merci et encore bravo a vous 2 pour les codes

pour apprendre et comprendre

je vais essayer de traduire en français ( la partie qui m’intrigue ) approximatif lol

wsa.Cells(wsp.Cells(x, "B") + pla, "D") = wsp.Cells(x, "C")

bon alors on prend la formule dans l'autre sens

wsp .cell ( x,c ) >> sur la feuil p1 prend toutes les valeurs des cellules de la colonne C à l'interieur de la boucle *X ' avec debut et fin

ca ok c'est peut etre pas super explique mais je comprend

wsa.Cells(wsp.Cells(x, "B") + pla, "D") >>> copie les résultats sur la feuil p3 , wsp.Cells(x, "B") range dans l'ordre en fonction de la valeur trouve en B ???

+ pla ( ok debut de tableau ) en colonne D ok pas de souci non plus

Le 04/04/2015 à 10:50

bonjour,

voici une tentative d'explication

wsa.Cells(wsp.Cells(x, "B") + pla, "D") = wsp.Cells(x, "C")

l'objectif de cette instruction est de mettre en P3 en colonne D, sur la ligne qui correspond au nombre trouvé en P1 colonne B, le contenu trouvé en P1 en colonne C. Et ceci pour chaque ligne de P1 (identifiée x et qui va du début à la fin du tableau)

dans

wsa.Cells(wsp.Cells(x, "B") + pla, "D") = wsp.Cells(x, "C")

on peut décomposer ainsi

nombre=wsp.cells(x,"B") ' nombre trouvé en position x sur p1

ligne_P3=nombre+pla ' calcul du numéro de ligne de p3 où il faudra copier l'info

nom=wsp.cells(x,"C") ' nom trouvé en position x sur p1 sur la ligne calculée

wsa.cells(ligne_P3,"D")=nom 'on met le nom en P3

Rechercher des sujets similaires à "plage variable index equiv boucle vba"