recherche position de nb dans liste de nb !

Claude · 14 Novembre 2007

Bonjour le forum,

Dans la continuité de ma première question "nb contenus ds un ensemble de nb" du 12/11/07 résolue magistralement par Banzai 64 , je voudrais savoir comment positionner un nombre contenu dans une liste de nombre pour en calculer son écart.

L'écart sera la distance calculée par rapport au dernier nombre de la liste, par exemple si le dernier nb recherché se trouve A8 et comme la liste est de 11 nb, l'écart sera de 11- 8 = 3

En A2:A11 sont les nombres de 4 à 6 chiffres, en B2:B4 des nombres à 2 chiffres, en C2:C4 doivent apparaitrent les écarts.

Par exemple, je recherche dans la colonne A, la position du nombre 12 qui est placé en B2 , je trouve en A, 5 fois le nb 12, le dernier se situant en A10, comme la colonne A contient 11 nombres l'écart sera sera de 11-10 = 1.

Voici ci-joint dans un fichier le travail

Merci de me renseigner.

Amicalement. Claude

Spitnolan08 · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour,

Je ne sais quelle technique utilisait la solution de Banzaï64 mais je te propose ce code à placer dans le module de la feuille

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ecart As Long, DerLig As Long, DerB As Long
Dim C As Range
Dim firtsaddress As String

DerB = Sheets(1).Range("B65500").End(xlUp).Row
If Intersect(Target, Sheets(1).Range("B1:B" & DerB)) Is Nothing Then Exit Sub

DerLig = Sheets(1).Range("A65500").End(xlUp).Row
With Sheets(1).Range("A1:A" & DerLig)
    Set C = .Find(Target.Value)
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
            Ecart = DerLig - C.Row
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
    Target.Offset(0, 1).Value = Ecart
End With
End Sub

Il s'active à chaque fois que tu changes une valeur en colonne B.
Si tu veux qu'il s'active également qu'il s'active à un changement de colonne A, il suffit de l'adapter.

Cordialement

skoobi · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour,
une solution en VBA.

Edit: salut Spitnolan08, plus rapide que moi

jp14 · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour

Une solution avec une fonction personnalisée.

JP

Claude · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Merci Spitnolan08, Skoobi, jp14,

La solution avec fonction personnalisée de jp14 me convient mieux car je ne connais rien en VBA.

Une formule sans macro m'aurais convenu davantage, mais c'est peut-être pas possible !!

Encore merci de votre aide rapide

Amicalement. Claude

Claude · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Re bonjour ,

Merci de me faire savoir si ma question est résolvable seulement par une formule ?

Amicalement. Claude

Excel_lent · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour à tous et autres,

Pas sûr d'avoir tout compris.
Quel écart faut-il pour 56 par exemple ? Moi je trouve 4. Est-cela Claude ?
Mi-figue, mi-raisin ou plutôt mi-formule mi-macro voir fichier joint.
On peut tout traiter par macro mais il y aura bien un éminent formuliste (redite) pour compléter mon début.

Ajout : il suffit de changer la valeur de B2 pour avoir son écart en C2.

Claude · 14 Novembre 2007

Re : recherche position de nb dans liste de nb !

Merci Excel_lent !

J'avais trouvé la formule personnalisée de jp14 séduisante, mais à l'usage comme j'ai plusieurs feuilles de calcul ouvertes en même temps, ça m'a fichu un peu la panique en ralentissant considérablement mon travail et même en y introduisant quelques erreurs ! J'ai supprimé la macro associée de jp14 et tout est rentré dans l'ordre. C'est pour cela que je recherche seulement une formule !
Pour répondre à ta question Excel_lent pour le 56 l'écart est 0, puisque 56 est contenu dans le nombre 56790 en A11 ce qui donne 11-11 = 0. Si tu recherche l'écart de 12 par exemple, comme on retrouve 5 fois 12 dans la colonne A, le dernier étant en A10 dans le nombre 123579, soit 11-10 = ce qui donne un écart égal à 1.

Merci de t'être interessé à mon problème.

Amicalement. claude

jp14 · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour

Pour éviter que la macro soit lancé en permanence il faut supprimer le code
"Application.Volatile".
Le calcul ne sera effectué que lorsque on utilise la touche entrée sur la cellule qui contient la formule

JP

Claude · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Merci jp14,

Je vais essayer, pour voir si ca marche.

A bientôt. Claude

Claude · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Re salut jp14,

J'ai supprimé "Application.volatile". Mais il y a vraiment problème ! Quand je passe d'une feuille à l'autre, dans la feuille où j'ai appliqué la macro, les résultats disparaissent de la colonne, le recalcul repart, sa mouline lentement, puis prêt! Je retourne sur la feuille en question, les résultats ne sont plus affichés, la colonne est blanche mais les formules ont en place. Pour tout réafficher, il faut quitter la feuille et la relancer !! Dommage !

A bientôt. Claude

Excel_lent · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Bonjour Claude, à tous et aux autres,

J'avais cru qu'il fallait l'écart entre les deux dernières sorties et non pas entre le dernier cas et la dernière sortie.
C'est plus simple maintenant. Vois si la macro jointe te pose moins de problème que celle de jp14.

Claude · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Merci Excel_lent,

J'ai dézippé mais je ne peux pas ouvrir le fichier !
J'ai Excel 97 !

Merci

Amicalement. Claude

Excel_lent · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Re,

Désolé, peux pas te dire.
A tout hasard voici le code de la fonction ecart à copier dans un module.

Code:

Function ecart(cellule As Range)
    Dim derli As Long, li As Long
    derli = Range("A65536").End(xlUp).Row
    For li = derli To 2 Step -1
       If Cells(li, 1) Like "*" & cellule.Value & "*" Then
         ecart = derli - li
         Exit For
       End If
    Next
End Function

En C2, saisir =ecart(B2) pour avoir l'écart du nombre en B2.

Claude · 15 Novembre 2007

Re : recherche position de nb dans liste de nb !

Merci Excel_lent,

La macro fonctionne bien pour l'exemple de travail indiqué, a savoir les nombres dans la colonne A, les nombres a rechercher dans placés en B et la fonction ecart(b2) placée en C2.

Par contre je travaille sur un tableau avec les colonnes suivantes dont je ne peux modifier l'ordre : AQ, AR, AS, AT, AU, AV, AW, AX
Les nombres à 6 chiffres sont en AQ, les nombres a rechercher sont en AV et la fonction ecart est en AX. Je fais donc en AX2, =ecart(AV2) j'obtiens les résultats pour la colonne AT ! Il y a un problème de décalage que je ne sais pas gérer vu mon ignorance en VBA. Je voulais simplement adapter cette macro à mon travail personnel.
La macro de jp14 s'y adapte très bien, mais quand j'ouvre d'autre feuilles c'est la panique !

Encore merci. A bientôt.

Amicalement. Claude

recherche position de nb dans liste de nb !

Claude

XLDnaute Occasionnel

Pièces jointes

Spitnolan08

XLDnaute Barbatruc

skoobi

XLDnaute Barbatruc

Pièces jointes

jp14

XLDnaute Barbatruc

Pièces jointes

Claude

XLDnaute Occasionnel

Claude

XLDnaute Occasionnel

Excel_lent

XLDnaute Impliqué

Pièces jointes

Claude

XLDnaute Occasionnel

jp14

XLDnaute Barbatruc

Claude

XLDnaute Occasionnel

Claude

XLDnaute Occasionnel

Excel_lent

XLDnaute Impliqué

Pièces jointes

Claude

XLDnaute Occasionnel

Excel_lent

XLDnaute Impliqué

Claude

XLDnaute Occasionnel

Discussions similaires