Microsoft 365 trouver la dernière ligne de la valeur "x"

[lesoldat9]

Bonjour à tous,

Je souhaite trouver une formule vba qui m'indique la dernière ligne où se trouve ma valeur "X".

exemple:
a
b
c
x
x
x
x

x=ligne 7

Merci à vous

 

[sylvanu]

Bonjour Lesoldat,
Si vos données sont en A1:Axx alors essayez :

=MAX(LIGNE(A1:A10)*(A1:A10="x"))

A valider par Maj+Ctrl+Entrée car formule matricielle.

 

[xUpsilon]

Bonjour,

En considérant que les valeurs sont "regroupées" (triées fonctionne aussi), avec en A1 la valeur cherchée et en B1:B100 la série de données dans laquelle on cherche :
=EQUIV(A1;B1:B100;0)+NB.SI(B1:B100;A1)-1

Bonne journée,

Edit : Bonjour sylvanu

 

[lesoldat9]

Bonjour Messieurs,

Ses formules fonctionnent sur vba?
J'ai un doute

Cordialement.

 

[sylvanu]

une formule vba

Oups !!! C'est comme à l'école : quand tu lis mal la consigne tu te plantes.
Une fonction perso :

Function TrouverDernier(Plage, C$)
    tablo = Plage
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = C Then TrouverDernier = i
    Next i
End Function

Syntaxe dans la feuille :

=TrouverDernier(Plage de recherche;Chaine à trouver)
par exemple
=TrouverDernier(B1:B16;"x")

ou encore en module :

Sub TrouverDernierOccurence()
    Chaine = "x"
    tablo = Sheets("Feuil2").Range("B1:B20")
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = Chaine Then L = i
    Next i
    MsgBox "Dernier " & Chaine & " trouvé en ligne " & L
End Sub

 

[vgendron]

bonjour

Bonjour Messieurs,

Ses formules fonctionnent sur vba?
J'ai un doute

Cordialement.

si ..
Sub test()
MsgBox Evaluate("MAX(row(A1:A10)*(A1:A10=""x""))")
End Sub

 

[sylvanu]

Re, bonjour Vgendron,
Par Evaluate, généralemenr c'est toujours lent.
Par exemple sur 100 lignes à rechercher le dernier "x", sur mon PC j'obtiens :

Edit : Ce sont des ms et non des µs.

 

[vgendron]

en fait. je n'utilise jamais Evaluate. c'était juste pour dire que si les formules fonctionnent telles quelles sous VBA
quitte à évaluer.. autant mettre la formule dans la feuille..
ensuite.. vu la taille du fichier posté en guise d'exemple.... loin de moi les considérations de rapidité d'execution..

 

[lesoldat9]

Oups !!! C'est comme à l'école : quand tu lis mal la consigne tu te plantes.
Une fonction perso :

Function TrouverDernier(Plage, C$)
    tablo = Plage
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = C Then TrouverDernier = i
    Next i
End Function

Syntaxe dans la feuille :

=TrouverDernier(Plage de recherche;Chaine à trouver)
par exemple
=TrouverDernier(B1:B16;"x")

ou encore en module :

Sub TrouverDernierOccurence()
    Chaine = "x"
    tablo = Sheets("Feuil2").Range("B1:B20")
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = Chaine Then L = i
    Next i
    MsgBox "Dernier " & Chaine & " trouvé en ligne " & L
End Sub

Hello sylvanu,
ta formule module à l'aire de fonctionner mais le numéro de la ligne trouvé est celle juste au dessus.
Je m'explique si x est sur la ligne 5,6 et 7 il me renvoie la valeur 6.
Cordialement.

 

[sylvanu]

Re,
testez cette PJ, les trois solutions remontent la valeur correcte.

 

[fanch55]

Bonjour à tous,
Petite fonction vba toute simple appelable par formule ou par macro :

Function FindLast(Cible As String, Source As Range, Optional InRange = False)
Dim R As Range
    Set R = Source.Find(Cible, Source.Cells(1), xlValues, xlWhole, , xlPrevious)
    Select Case True
        Case R Is Nothing:  FindLast = ""
        Case InRange:       FindLast = R.Row - Source.Cells(1).Row + 1
        Case Else:          FindLast = R.Row
    End Select
End Function

Mais kékécé que Inrange ? C'est pour renvoyer la position dans le range ou la feuille ...

Sub Test()
    MsgBox FindLast("x", [A1:A7])
End Sub

 

[patricktoulon]

bonjour
dans l'esprit de @fanch55

Function Find_LastRowWithValue(rng As Range, valeur)as variant
    Set cel = rng.Resize(Cells(Rows.Count, rng.Column).End(xlUp).Row).Find(valeur, LookIn:=xlValues, SearchDirection:=xlPrevious)
    If Not cel Is Nothing Then Find_LastRowWithValue = cel.Row Else Find_LastRowWithValue = Empty
End Function

Sub test()
    MsgBox Find_LastRowWithValue([A1:A20], "x")
End Sub
Sub test1()
    MsgBox Find_LastRowWithValue([B:B], "x")
End Sub

la formule

=Find_LastRowWithValue(A:A;"x")

vous l'avez remarqué je prend tout la colonne dans la formule
car je la réduit par le resize a la plage utilisé dans la colonne demandée dans la fonction
comme ça plutôt que sélectionner une plage précise dans la formule comme la Sub vba on sélectionne la colonne
pratique sur un range évolutif sans name
et xlvalues suffit

 

[patricktoulon]

bonjour
je voudrais revenir sur la boucle sur un tableau
plus particulièrement sur ce qu'a proposer @sylvanu

supposons par exemple un tableau de 80 000 lignes en colonne "A"
et que le dernier "X" se situerait en ligne heu... 7897
sa fonction tournerait 7997 fois avant d'arriver au dernier "X" elle continuerait jusqu'a la fin
d'autant plus que l'on peut pas mettre de exit for tant que l'on sait pas si il y a encore un "X"

supposons maintenant que le dernier "X" se trouve en ligne 2 la fonction tournerais 2 fois avant de trouver et continuerais j'usqu'a 80 000 tours et la encore on ne peut pas mettre de exit for pour la même raison que la précédente

oui mais alors comment faire ?
me vient une idée toute simple
tester le nieme de la boucle et le dernier moins le nieme en même temps
si celui d'en bas est > celui d'en haut et celui d'en haut>0 et celui d'en bas >0 alors c'est celui d'en bas et là oui !!! on peut faire un exit for
dans le pire des cas on bouclerait que la moitié avec ma version

Function TrouverDernierVpat(tablo, V$)
    Dim qb&, qh&, i&
    For i = 1 To UBound(tablo)
        If tablo(UBound(tablo) - (i - 1), 1) = V Then qb = (UBound(tablo) + 1) - i
        If tablo(i, 1) = V Then qh = i
        TrouverDernierVpat = qh
        If qb > qh And qh > 0 Then TrouverDernierVpat = qb: Exit For
    Next i
    MsgBox i & " tours de boucle"
End Function
Sub testq1()
'formule  :   =TrouverDerniervpat(A1:A80000;"X")
    MsgBox TrouverDernierVpat(Feuil1.Range("A1:A80000").Value, "X")
End Sub

'*********************************************************
'*********************************************************
Function TrouverDernier(Plage, C$)
    tablo = Plage
    For i = 1 To UBound(tablo)
        If tablo(i, 1) = C Then TrouverDernier = i
    Next i
    MsgBox i & " tours de boucle"
End Function
Sub testq2()
'formule  :  =TrouverDernier(A1:A80000;"X")
    MsgBox TrouverDernier(Feuil1.Range("A1:A80000"), "X")
End Sub

le msgbox est parlant

 

[sylvanu]

Bonsoir Patrick,
Alors poussons le raisonnement jusqu'au bout.
Je segmente la plage en 8. Je regarde dans chaque segment si un "x" existe ( avec countif ) en partant du dernier paquet.
On sort du For par un "exit", et on charge le bon segment en array.
On cherche ce "x" dans ce segment en partant de la fin. Dès trouvé on sort de la boucle.
Avec N (N° de segment ) et "i" l'indice de boucle, on calcule la position du dernier "x".

Sub ChercheX()
    Dim Chaine, DL, N, Segment, i
    Chaine = "x"
    Application.ScreenUpdating = False
    DL = Range("A65500").End(xlUp).Row  ' Dernière ligne
    Segment = Int(DL) / 8               ' On segmente en 8 paquets ( mais 8 est il l'optimum ? )
    For N = 8 To 2 Step -1              ' On regarde dans chaque paquet si "Chaine" existe en partant de la fin
        Existe = Application.CountIf(Range("A" & (N - 1) * Segment & ":A" & N * Segment), Chaine)
        If Existe > 0 Then Exit For     ' S'il existe on sort
    Next N
    tablo = Range("A" & 1 + (N - 1) * Segment & ":A" & N * Segment) ' On charge le bon paquet en array
    For i = UBound(tablo) To 1 Step -1                              ' On cherche dans cet array la "Chaine"
        If tablo(i, 1) = Chaine Then L = i: Exit For                ' Si elle existe on sort
    Next i
    ' La chain se trove en position : (N - 1) * Segment + i
    MsgBox "Dernier " & Chaine & " trouvé en ligne " & (N - 1) * Segment + i
End Sub

Le gain de temps est appréciable. ( environ 6 fois plus rapide sur mon PC et XL2007 )

 

[patricktoulon]

re
@sylvanu tiens teste celui là
comme tu vois je double boucle mais sur le meme tableau pas de sous tableau
c'est simple j'ai 00.00 sec a chaque fois sur 80 000 lignes
ca vas trop vite je ne peux pas tester
mais je me demande si couper le tableau meme par 100 ne serait pas mieux finalement
comme la 2d boucle par step.1 elle mettait moins de temps a trouver

Sub xx()
   'juste pour remplir la colonne  avec un x au hazard
   [A1:A80000] = "a"
   Randomize
   Cells(Round(1 + (Rnd * 79999)), 1) = "x"
End Sub
Sub ChecheX()
    Dim pioche As Boolean, i&, e&, maligne&, segment, t#, Chaine
     tbl = [A1].Resize(ActiveSheet.UsedRange.Rows.Count, 1).Value
    partie = UBound(tbl) / 10
    Chaine = "x"
    t = Timer
    For i = UBound(tbl) To 1 Step -partie
        deb = Application.Max(1, i - partie)
        If Application.CountIf(Range(Cells(i, 1), Cells(deb, 1)), Chaine) Then
            segment = Range(Cells(i, 1), Cells(deb, 1)).Address(0, 0)
             pioche = True
            For e = i To i - partie Step -1
                If tbl(e, 1) = Chaine Then maligne = e: Exit For
            Next
        End If
        If pioche Then Exit For
    Next
    MsgBox "x trouvé en ligne " & maligne & " dans le segment " & segment & vbCrLf & "en " & Format(Timer - t, "#0.000 sec")
End Sub