Fonction pour récupérer le numéro de la n ième ligne visble après un filtre automatique

**patricktoulon** · 18/08/2018, 17h49

bon ben je n'ai rien trouvé sur l'object listrows avec un properties visible ou pas
https://docs.microsoft.com/en-us/off...excel.listrows

edit :
bon ben maintenant c'est plus clair
c'est encore special cell qui joue de vilain tours en fonction du range employé (tester tour a tour un des trois (set plage=....)) en debut de sub
de plus contrairement a un range classique on ne peut pas utiliser les index de cellule (voir le dernier debug)
bref j'avoue que j'ai pas pigé ou c'est sensé nous simplifier la vie
sachant qu'une propriété visible n'existe pas sur cet object du moins je ne l'ai pas trouvé ainsi que moulte outils et fonction que nous avons avec un range classique
et meme pire lorsque l'on obtient un range avec databodyrange la c'est carrément du n'importe quoi

perso je crois que je vais rester avec mes names (nom attribué a une plage)que j'utilise pour faire des tableaux distincts comme ca j'ai tout les outils vba excel a dispo

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub TestFonctionPatrick()
    Dim plage As Range, plage_visible_col_1 As Range
    '
    Debug.Print "cherche la 5° ligne visible " & vbCrLf
    '
    ' A tester les 3 ci dessous l'un apres l'autre
    'Set plage = Range("tableau1").ListObject.DataBodyRange
    'Set plage = ActiveSheet.Range("tableau1[#all]")
    Set plage = ActiveSheet.ListObjects("tableau1").Range
    '
    Set plage_visible_col_1 = plage.Columns(1).SpecialCells(xlVisible)
    '
    '
    Debug.Print "plage complete avec entete    :" & plage.Address
    '
    '
    Debug.Print "plage visible colonne(1)      :" & plage_visible_col_1.Address & vbCrLf
    '
    '
    For Each cel In plage_visible_col_1.Cells
        i = i + 1: If i = 5 Then Debug.Print "test avec une boucle et sortie quand i arrive a 5 = ligne " & cel.Row & vbCrLf
    Next
    '
    Debug.Print "test avec cells(index(5)) de la plage visible     = ligne " & plage_visible_col_1.Cells(5).Row
End Sub

on a plus qu'a souhaiter que pierre trouve des solutions pour palier au manque d'outils de cet object dans son TUTO

ryu je vais regarder mais j'ai un doute car le test specialcells ne donne rien chez moi j'ai toujours la plage complete selon le mode d'obtention du range (voir mes 3 set plage en debut de sub )

**patricktoulon** · 18/08/2018, 17h53

re
@RYU
bon ben sensiblement le meme raisonnement sauf que tu decompose les areas et leur rows visible respectives
je vais directement a l'essentiel avec for each cel in laplage visible

**RyuAutodidacte** · 18/08/2018, 18h54

Re Patrick

Dis moi si tu est d'accord avec ce qui suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Pat_RecupLigTab()
Dim Result&
    Result = Pat_LigTableau("Table1", 7)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
 
    Result = Pat_LigTableau("Table1", 2)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
End Sub
 
Function Pat_LigTableau(NomTableau$, Ligne&) As Long
Dim i&
    For Each cel In ActiveSheet.ListObjects(NomTableau).Range.Columns(1).SpecialCells(xlVisible).Cells
        i = i + 1: If i = Ligne Then Pat_LigTableau = cel.Row: Exit For
    Next
 
End Function

Le tableau de test, j'ai filtré sur PRODUCTION :

NOM	GENRE	VILLE	SALAIRE	QUALIF	DATENaiss
Benard	Madame	Paris	3500 C	PRODUCTION	10/10/1950
Bertrand	Monsieur	Boulogne	1900 C	VENTE	29/10/1950
Dampierre	Monsieur	Issy	1900 C	PRODUCTION	15/10/1960
Dang	Monsieur	Sevres	1128 E	MARKETING	16/10/1960
Do	Madame	Montigny	3250 C	PRODUCTION	18/10/1960
Dujardin	Madame	Neuilly	3260 C	PRODUCTION	16/10/1950
Garcia	Madame	Montigny	3290 E	VENTE	19/10/1950
Giraud	Madame	Issy	3310 E	PRODUCTION	21/10/1950
Greuzard	Madame	Paris	3320 E	VENTE	22/10/1980
Joset	Monsieur	Issy	1128 E	PRODUCTION	25/10/1980
Lefevre	Madame	Neuilly	3350 E	PRODUCTION	27/10/1980
Naim	Monsieur	Paris	2200 C	PRODUCTION	30/10/1980
Vals	Monsieur	Paris	1300 E	MARKETING	2/11/1980
Verin	Monsieur	Paris	1128 E	PRODUCTION	3/11/1980
Voisin	Monsieur	Montigny	1800 C	MARKETING	4/11/1930

**patricktoulon** · 18/08/2018, 19h55

re
@RYU
j'ai testé et oui par rapport a la plage entete compris c'est bon si on se refere a la plage que donne bodydatarange qui est enfait le range reel dans les names non il faut ajouter -1

en fait c'est ma ligne de code dans une fonction peut etre ajouter un argument de facon a avoir l'option header/noheader

au fait j'obtiens 13 et 3

**RyuAutodidacte** · 18/08/2018, 21h51

Re,
@Patrick

Ha je vois que tu as supprimé ton dernier post !!! pas eu le temps de m'amuser avec …

Le résultat doit être 12 et 4

Le code (Pat_RecupLigTab)si il est ok tel que je l'ai écrit par rapport au post #21 me donne en résultat 11 et 2

j'ai testé et oui par rapport a la plage entete compris c'est bon si on se refere a la plage que donne bodydatarange qui est enfait le range reel dans les names non il faut ajouter -1
… …
au fait j'obtiens 13 et 3

Dans ce cas par rapport à mon résultat à 13 -1 => 12 | à 3 -1 => 2 sauf que le résultat est ligne 4

Comme ca c'est ok :

Code :

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

1
2
3
4
5
6
Function Pat_LigTableau(NomTableau$, Ligne&) As Long
Dim i&
    For Each cel In ActiveSheet.ListObjects(NomTableau).DataBodyRange.Columns(1).SpecialCells(xlVisible).Cells
        i = i + 1: If i = Ligne Then Pat_LigTableau = cel.Row: Exit For
    Next
End Function

**patricktoulon** · 18/08/2018, 21h53

bon voila j'ai supprimé l'acien post c'etait trop une catastrophe

j'ai déraillé completement

voila comment je vois les choses

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Pat_RecupLigTab()
    Dim Result&
    'test sans header
    Result = Pat_LigTableau("Tableau1", 7, , False)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
    '
    Result = Pat_LigTableau("Tableau1", Ligne:=2, head:=False)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
    '
    '**********************************************************************************************************************************
    '
    'test avec  header
    Result = Pat_LigTableau("Tableau1", 7, , True)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
    '
    Result = Pat_LigTableau("Tableau1", Ligne:=2, head:=True)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
 
Function Pat_LigTableau(ByRef NomTableau$, ByVal Ligne&, Optional ByRef sh As Worksheet, Optional ByRef head As Boolean = False) As Long
    Dim i&, plage As Range
    If sh Is Nothing Then Set sh = ActiveSheet
    Set plage = sh.ListObjects(NomTableau).Range.Columns(1).SpecialCells(xlVisible):
    If head = False Then Ligne = Ligne + 1
          For Each cel In plage.Cells
        i = i + 1: If i = Ligne Then Pat_LigTableau = cel.Row: Exit For
    Next
End Function

diverses facon de coder l'appel

Code :

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

1
2
3
4
5
6
Result = Pat_LigTableau("Tableau1", 2) 'ligne2 du tableau1 dans activesheet sans le header
Result = Pat_LigTableau("Tableau1", 2, Sheets("BD")) 'ligne2 du tableau1 dans sheets("BD") sans le header
Result = Pat_LigTableau("Tableau1", 2, head:=True) 'ligne2 du tableau1 dans activesheet avec  le header explicit
Result = Pat_LigTableau("Tableau1", 2, head:=True) 'ligne2 du tableau1 dans activesheet avec  le header explicit
Result = Pat_LigTableau("Tableau1", 2, , True) 'ligne2 du tableau1 dans activesheet avec  le header implicit
Result = Pat_LigTableau("Tableau1", 2,Sheets("BD") , True) 'ligne2 du tableau1 dans sheets("BD") avec  le header implicit

bien entendu tout les arguments optional peuve etre enumeré dans l'apel dans le desordre

Code :

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

Result = Pat_LigTableau("Tableau1", 2, head:=True,sh:=sheets("BD")) 'ligne2 du tableau1 dans sheets("BD")avec  sheet et le header explicit (argument dans le desordre

piuffff fatigué moi

**RyuAutodidacte** · 18/08/2018, 23h33

Re
@Patrick

là ton code est ok

• Par contre je ne suis pas sur que le head soit nécessaire car qd tu comptes dans les données en général tu ne prends pas en compte la ligne d'en-tête

• Maintenant mon code ne parcours pas toutes les lignes visibles puisque l'on additionne le nombre de ligne dans une area à chaque fois et on ajuste avec la dernière area si nécessaire (c'est pas mal comme principe)

• Si on veut faire simple et éviter la gestion des onglets (du coup on évite les onglets et le head avec le 1er point) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub RecupLigTab_2()
Dim Result&
    Result = LigTableau_2("Table1", 7)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
 
    Result = LigTableau_2("Table1", 2)
    If Result > 0 Then MsgBox "Ligne correspondante : " & Result Else MsgBox "Il n y a pas de ligne correspondante au tableau trié"
End Sub
 
Function LigTableau_2(NomTableau$, Ligne&) As Long
Dim i&
    For Each cel In Range(NomTableau).Columns(1).SpecialCells(xlVisible).Cells
        i = i + 1: If i = Ligne Then LigTableau_2 = cel.Row: Exit For
    Next
End Function

• Ce qui rejoint le post #2 de @boisgontierjacques

dans le principe, sauf qu'il utilise une collection à la différence en parcourant toute la plage visible
PS @boisgontierjacques : il faudrait une gestion d'erreur dans le cas ou la ligne voulu va au delà de la plage visible - mis à part cela tout est ok

@LeBo74 :

Je cherche à créer une fonction pour récupérer le numéro de la n ième ligne visible après l'utilisation d'un filtre sur un tableau structuré.

j'aimerai savoir dans quel cas cela peut être utile (pour ne pas mourrir bête

) ?

**patricktoulon** · 19/08/2018, 00h21

re

• Par contre je ne suis pas sur que le head soit nécessaire car qd tu comptes dans les données en général tu ne prends pas en compte la ligne d'en-tête

pour respecter les vrai index
le databodyrange donne la vrai plage elle commence en A2 selon le names que l'on peut voir dans le gestionnaire de nom
si je m'en refere a ca le resultat est faux car dans cet exemple la 7 donne 13 alors que c'est la 16

• Maintenant mon code ne parcours pas toutes les lignes visibles puisque l'on additionne le nombre de ligne dans une area à chaque fois et on ajuste avec la dernière area si nécessaire (c'est pas

mon code ne parcours pas toutes les lignes non plus il ne parcour meme pas les areas il compte les cellule visibles de la colonne(1) 1 cellule=1ligne et il sort avec exit

autrement dit je parcours 7 cellules (7lignes)pour le 1er result et 2 pour le 2d

je vois vraiment pas comment faire moins

j'aurais pu faire l'inverse prendre le reel("A2:G34") et ligne-1 donc visuellement au niveau des lignes 7 devient 6 pour tomber juste: oui si tu veux

apres je suis d'accords on peut s'en passer en gardant en tete qu'il faut mettre la plage avec header en examen pour que les indexs de ligne correspondent

la plage avec header formule simple =range("tableau1[#all]").address

la plage sans header formule simple =Range("tableau1").Address

pour ilustrer ce que je dis

Code :

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

1
2
3
4
5
6
7
8
9
Sub test()
    Debug.Print "adresse du tableau avec entete compris:              : " & Range("tableau1[#All]").Address
    Debug.Print "adresse du tableau  sans entete:                     : " & Range("tableau1").Address
    Debug.Print "adresse de la cells(1)  du tableau entete compris    : " & Range("tableau1[#All]").Cells(1).Address
    Debug.Print "adresse du databodyrange :                           : " & Range("tableau1").ListObject.DataBodyRange.Columns(1).Address
    Debug.Print "adresse de la cells(1)  du tableau reel :(methode1)  : " & Range("tableau1").Cells(1).Address
    Debug.Print "adresse de la cells(1)  du databodyrange :(methode2) : " & Range("tableau1").ListObject.DataBodyRange.Cells(1).Address
    Debug.Print "les cellules visibles :                              : " & Range("tableau1").ListObject.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Address
End Sub

**Pierre Fauconnier** · 19/08/2018, 12h01

Salut

Envoyé par patricktoulon

[...]
c'est encore special cell qui joue de vilain tours en fonction du range employé (tester tour a tour un des trois (set plage=....)) en debut de sub
de plus contrairement a un range classique on ne peut pas utiliser les index de cellule (voir le dernier debug)[...]

Il n'y a aucun vilain tour joué par SpecialCells. Le problème est que tu penses que .Cells(i) donne la ième cellule de la plage à laquelle tu te réfères, alors qu'en fait, parce que tu es dans un vecteur colonne, c'est la cellule qui se trouve à la ième ligne de la cellule supérieure gauche de la plage renseignée, cette cellule supérieure gauche étant sur la ligne 1 de la plage. Il ne s'agit nullement d'un index! Il se fait que lorsque les cellules sont visibles, ton raisonnement peut sembler correct puisque le résultat est le même, mais il ne tient pas avec des cellules masquées qui existent toujours sur la feuille.

Voici un tableau avec des lignes masquées.

Nom : 2018-08-19_115738.png
Affichages : 483
Taille : 12,1 Ko

Nom : 2018-08-19_115738.png
Affichages : 483
Taille : 12,1 Ko

Si tu regardes ce tableau et que tu utilises ? range("tableau1").Columns(1).specialcells(xlCellTypeVisible).cells(4).address, tu obtiens $f$13 (qui est masqué), ce qui est tout à fait normal, puisque 4 n'est pas un index mais l'indication d'une position de cellule (sur la feuille) se trouvant sur la quatrième ligne (même colonne) par rapport à F10 (1ière cellule de la place .specialcells(xlcelltypevisible). Donc, range("f10")(1) = F10, range("f10")(2) = F11 ==>> range("f10")(4) = F13 (masquée ou non).

Si tu avais utilisé ? range("tableau1").Columns(1).specialcells(xlCellTypeVisible).cells(-4,-2).address, tu aurais récupéré $C$4, car par rapport à range("f10") (= range("f10)(1,1)), tu te positionnes 6 lignes plus haut (0 = 9, -1 = 8, -2 = 7, -3 = 6, -4 = 5,-5 = 4) et 3 colonnes à gauche (0 = E, -1 = D, -2 = C).

[EDIT]
Pour compléter mon explication....

Lorque l'on n'utilise qu'une seule position par rapport à une plage donnée (range(5), on se déplace en zigzag dans la plage renseignée, de gauche à droite puis de haut en bas. Si on renseigne deux valeurs (range(5,2)), la première renseigne la position en igne et la seconde la position en colonne. Les tableaux structurés ne changent rien à la donne.

Donc ? range("i6:m8")(6).address donne I7, et ? range("i6:m8")(2,1).address donne aussi I7.

Si, lors du positionnement positif, on sort de la plage de base, on reste malgré tout dans les colonnes de cette plage... ? range("i6:m8")(11).address donne I8.

Mais, lors d'un positionnement négatif, on a la plage en miroir avec comme axe la cellule supérieure gauche de la plage (qui sera toujours Plage(1)). I6:M6 compte 5 colonnes, si on utilise ? range("i6:m8")(-3).address, on ira en E6, et si on utilise ? range("i6:m8")(-4).address, on ira en I5 (car on a "consommé" les cinq colonnes de la plage miroir, on se déplace donc de droite à gauche puis de bas en haut dans la plage miroir)
[/EDIT]

Pour revenir à la question initiale...
Il n'y a donc pas, à ma connaissance, d'autre solution qu'un

Code :

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

fore each cell in ... .specialcells(xlcelltypevisible)

avec un Exit For lorsqu'on arrive à la cellule souhaitée (Hé oui, parfois, il faut bien accepter un Exit, sauf à boucler inutilement sur toute la plage

)

Envoyé par RyuAutodidacte

[...]
@LeBo74 :
j'aimerai savoir dans quel cas cela peut être utile (pour ne pas mourrir bête

) ?

Moi aussi

**LeBo74** · 21/08/2018, 03h24

Bonjour à tous et merci pour votre aide,

Pour les curieux qui veulent savoir à quoi peut servir la fonction que je cherche , voici la réponse :
Je travaille à faire des analyses de performance des établissements de santé. Dans le cadre de ces analyses, nous cherchons souvent à comparer les résultats d'un établissement à celui situé au "1er tercile" (par exemple, si j'ai 12 établissement dans mon analyse, nous ciblerons les résultats du 4ième établissment comme mesure pour comparer la performance de notre établissement).
Par contre, lorsque je fais ces analyses, il arrive que ma base contienne des données sur plus d'une année que je peux filtrer via l'aide d'un segment ou du filtre automatique pour passer rapidement d'une année à l'autre.
Comme mes données sont classées en ordre croissant, l'utilisation d'une fonction me permettant de récupérer le # de la ligne de l'établissement cible me permettrait d'automatiser un calcul en bas du tableau qui se recalculerait à chaque changement dans mon filtre. J'ai la formule qui me permet de récupérer le rang de l'établissement situé au tercile, il me manque son # de ligne. C'était un petit défi du vendredi histoire de garder les neurones actifs et de me familiariser avex les possibilités d'Excel...

Mais, à ce que je constate au niveau des discussions, je reste toujours avec le même problème, la petite fonction que je pensais simple reste introuvable. J'ai essayer les différentes solutions présentées mais j'ai toujours le même fonctionnement : la fonction appelée dans une macro donne le résultat voulu mais appelé à partir d'une feuille, elle ne fonction pas :

Nom : Capture.JPG
Affichages : 478
Taille : 35,1 Ko

Donc, à moins que quelqu'un ai une autre idée, je vais fermer cette discussion et passer par une petite macro pour automatiser mon calcul plutôt que la fonction personelle.

Désolé si je n'ai pas répondu plus tôt, j'étaits parti dans le bois pour le week-end, loin des internets.

Bonne journée à tous

Invité · 21/08/2018, 03h38

Envoyé par LeBo74

Bonjour,

Merci pour ton explication.
Je ne vois pas trop comment est composée ta base mais si tu connais l'établissement de référence, quelle est la nécessité de travailler ensuite à partir du filtre ?

**Pierre Fauconnier** · 21/08/2018, 06h33

Salut.

Tout ça pour ça?

Il est toujours plus rentable de partir d'une base de données saine que de chipoter sur une base polluée

Si je devais faire ton boulot, j'extrairais les données de mon analyse dans un tableau bien propre ainsi que la ligne qui sert de comparaison et je travaillerais sur ce tableau, plutôt que de le laisser pollué par des données d'années non désirées et de chercher des trucs impossibles pour pallier les problèmes inhérents à mes données. Au passage, c'est la base des bases de l'analyse de données, ce que je dis ici

Je rappelle également ici la fonction SOUS.TOTAL qui permet quelques fonctions statistiques de base en ne prenant en compte que les lignes filtrées par le filtre automatique et, selon l'option choisie, les lignes filtrés manuellement.

**RyuAutodidacte** · 21/08/2018, 10h18

Bonjour,

j'ai l'impression q'un tableau croisé dynamique aurait été suffisant pour faire tout cela
peut être juste une macro, en donnant les paramètres voulus (via un procéder que l'on aura déterminé - useform par exemple) pour faire le/les TCD automatiquement voulu(s)
et dans le cas où l'on ne veut pas garder le/les TCD, il est facile de garder seulement les données des TCD en simple valeurs dans un nouveau classeur ou autre

**Pierre Fauconnier** · 21/08/2018, 10h20

Envoyé par RyuAutodidacte

[...]

j'ai l'impression q'un tableau croisé dynamique aurait été suffisant pour faire tout cela[..]

J'y ai pensé aussi. Ca dépend évidemment des analyses qui sont faites, mais le TCD propose déjà tellement de possibilités (en XL2016, en plus...)

**RyuAutodidacte** · 21/08/2018, 12h45

Re,

@LeBo74

Envoyé par LeBo74

Mais, à ce que je constate au niveau des discussions, je reste toujours avec le même problème, la petite fonction que je pensais simple reste introuvable. J'ai essayer les différentes solutions présentées mais j'ai toujours le même fonctionnement : la fonction appelée dans une macro donne le résultat voulu mais appelé à partir d'une feuille, elle ne fonction pas :

Tu n'as pas cherché à comprendre, pour cela il aurait fallu mettre un point d'arrêt au début de la fonction et utiliser la fonction sur ta feuille puis faire un pas à pas (le Debug.Print est conseillé)
Voir le résultat obtenu et comparé avec ce qui est visible sur ton tableau test.
Moi ce que j'ai remarqué c'est que SpecialCells(xlVisible) n'a pas été pris en compte lorsque l'on utilise la fonction sur la feuille.
Pour s'en rendre compte il suffit de prendre le faux résultat et d'ajouter le nombre de ligne masqué se trouvant avant le vrai résultat - si on fait l'addition le compte est bon

Pour l'instant je n'en connais pas la cause …

En reprenant l'exemple de mon post #23 le tableau et le critère (prise en compte de la colonne pour le Crit.)

Nom : Lig_Tab.png
Affichages : 461
Taille : 100,7 Ko

Nom : Lig_Tab.png
Affichages : 461
Taille : 100,7 Ko

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
    MsgBox Lig_Tableau("Table1", "PRODUCTION", 5, 3)
End Sub
 
Function Lig_Tableau(NomTableau$, Crit$, Col&, Ligne&) As Long
'Application.Volatile
With Range(NomTableau)
    For Each Cel In Columns.Item(1).Cells
        If Cel.Offset(, Col - 1) = Crit Then i = i + 1
        If i = Ligne Then Debug.Print Cel.Address: Lig_Tableau = Cel.Row: Exit For
    Next
End With
End Function

Edit : ceci est un code exemple qui marche (que je n'ai pas chipoté), il faudra l'adapter à ta situation, maintenant que tu as le principe (sachant qu'il y a plusieurs possibilités de coder/de procéder … à toi de trouver celle qui te convient)
+remise de la variable "NomTableau" à la place qui lui est dû dans la fonction

**patricktoulon** · 21/08/2018, 13h58

salut ryu
la meme plus universelle

Code :

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

1
2
3
4
Sub test()
    MsgBox Lig_Tableau("Table1", "PRODUCTION", 5, 3)
MsgBox Lig_Tableau("Table1", "PRODUCTION", "QUALIF", 3)
End Sub

Code :

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

1
2
3
4
5
6
7
8
Function Lig_Tableau(NomTableau$, Crit$, Col as variant , Ligne&) As Long
    Dim cel As Range
    With Range("Table1")
        For Each cel In Range("Table1").ListObject.ListColumns(col).Range
            If cel.Value = Crit And cel.EntireRow.Hidden = False Then i = i + 1: If i = Ligne Then Lig_Tableau = cel.Row
        Next
    End With
End Function

**eriiic** · 21/08/2018, 14h22

Bonjour à tous,

en plus de Sous.total() il y a maintenant aussi Agregat() avec de nouvelles possibilités.

J'imagine que tu as ou peux avoir le nom (n° ?) de l'établissement dans une cellule, disons H1.
En filtrant sur les années tu peux avoir la ligne de l'année filtrée la plus basse (si j'ai bien compris il n'y en aura qu'une) avec :

Code :

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

=MIN(SI((Table1[NOM]=$H$1)*(ANNEE(Table1[date])=ANNEE(SOUS.TOTAL(5;Table1[date])));LIGNE(Table1[NOM])))

matricielle à valider avec Shift+Ctrl+Entrée.

Tu peux mettre cette formule dans un nom 'ligRef' et tu l'auras à dispo tout autant pour une formule sur feuille que dans une macro :

Code :

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

MsgBox [ligRef]

Agregat() a les fonctions Quartile(), si tu préfères le calculer directement dans cette formule et ne plus avoir de référence à utiliser.
eric
eric

**RyuAutodidacte** · 21/08/2018, 17h41

Re Patrick,

Ca le fait le fai bien en "universelle"

par contre je crains que ce type de fonction ne sert pas à bcp de monde

PS : @Everybody : Est ce quelqu'un à la réponse pour :

Moi ce que j'ai remarqué c'est que SpecialCells(xlVisible) n'a pas été pris en compte lorsque l'on utilise la fonction sur la feuille.

vous savez pourquoi ???
Y'a-t-t-il une façon de coder pour la prise en compte … ? …

Edit : @Patrick : Corr. code faut enlever les With et NomTableau mis à la bonne place dans la fonction au lieu du nom du tableau réelle

Code :

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

1
2
3
4
5
6
Function Lig_Tableau2(NomTableau$, Crit$, Col As Variant, Ligne&) As Long
    Dim cel As Range, i as Long
        For Each cel In Range(NomTableau).ListObject.ListColumns(Col).Range
            If cel.Value = Crit And cel.EntireRow.Hidden = False Then i = i + 1: If i = Ligne Then Lig_Tableau2 = cel.Row
        Next
End Function

**Pierre Fauconnier** · 23/08/2018, 05h51

Patrick,

Envoyé par patricktoulon

[...]
c'est encore special cell qui joue de vilain tours en fonction du range employé (tester tour a tour un des trois (set plage=....)) en debut de sub
de plus contrairement a un range classique on ne peut pas utiliser les index de cellule (voir le dernier debug)[...]

Si ça t'intéresse, j'ai clarifié ce point dans ce billet sur mon blog

**RyuAutodidacte** · 23/08/2018, 10h00

Bonjour Pierre,

peut être que je me trompe, mais ne pourrait-on pas rajouter dans la continuité de ton blog les explications avec :

Range(Plage).SpecialCells(xlCellTypeVisible).Areas(X)(I)
ou
Range(Plage).SpecialCells(xlCellTypeVisible).Areas(X)(I, J)
ou
Range(Plage).SpecialCells(xlCellTypeVisible).Areas(X)(,J)

Fonction pour récupérer le numéro de la n ième ligne visble après un filtre automatique [XL-2016]

Macros et VBA Excel

Discussions similaires

Partager

Partager