Plage variable - index equiv VS boucle VBA
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
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
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 ?
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
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é.
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
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
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.
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é
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
Cela semble bien fonctionner
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")
+ pla ( ok debut de tableau ) en colonne D ok pas de souci non plus
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