Autres formule recherche valeur sur plage x lignes et y colonnes renvoyant l'address ligne ou colonne ou

[patricktoulon]

bonjour a tous
je m'applique depuis quelques temps a simplifier mes codes vba en utilisant des formules avec evaluate

je cherche donc aujourd'hui une formule de recherche une valeur dans une plage sur x lignes y colonnes

exemple
je cherche "toto" en A1:F15 si ca pouvait renvoyer l'adresses ca serait super

 

[fanfan38]

Bonjour,
Sub marecherche()
Dim rep As String, derlig As Long, dercol as long, marecherche As Range
derlig = Range("A" & Rows.Count).End(xlUp).Row
dercol = Cells(1, Columns.Count).End(xlToLeft)
rep = InputBox("Saisir le mot à chercher")
Set marecherche = Range(Cells(1, 1), Cells(derlig, dercol)).Find(rep, LookAt:=xlWhole)
MsgBox (marecherche.Address)
End Sub

A+ François

 

[richard.theo2]

Une possibilité pour que le inputBox ne soit pas un mot à chercher mais une chaine de caractères présentes dans une autre colonne ?

 

[fanfan38]

tu saisis ce que tu veux dans le inputbox....
La recherche se fait, dans l'exemple ci dessus, dans toute la feuille....

A+ François

 

[patricktoulon]

bonsoir fanfan38 et merci de ton retour
mais tu ne m'a pas compris
je la veux en formule excel pour l'utiliser en vba

je sais moi ma demande et quelque peut étrange mais moi et mes expériences
j'en ai bien une que j'ai mis au point pour chercher x valeur sur la meme ligne mais la c'est une valeur n'importe ou dans une ligne

 

[Dranreb]

Bonsoir
Ça n'existe pas à ma connaissance en fonctions Excel de base.
Alors je propose cette fonction perso, utilisable en Formule et en VBA :

Function CherchePlage(ByVal Quoi, ByVal Rng As Range) As Range
   Set CherchePlage = Rng.Find(Quoi, LookAt:=xlWhole)
   End Function

Exemple en VBA :

Sub Test()
   MsgBox CherchePlage("Toto", ActiveSheet.[A1:F15]).Address
   End Sub

 

[job75]

Bonsoir patricktoulon, fanfan38, richard.theo2, Bernard,

Voyez le fichier joint et cette formule matricielle donnant la 1ère adresse trouvée pour Bonsoir :

=ADRESSE(PETITE.VALEUR(SI(A1:F12="Bonsoir";LIGNE(A1:F12));1);EQUIV("Bonsoir";INDEX(A1:F12;PETITE.VALEUR(SI(A1:F12="Bonsoir";LIGNE(A1:F12));1););0))

Bonne nuit.

 

[patricktoulon]

bonjour a tous
merci job75 ca match
en vba

MsgBox Evaluate("=ADDRESS(SMALL(IF(A1:F12=""Bonsoir"",ROW(A1:F12)),1),MATCH(""Bonsoir"",INDEX(A1:F12,SMALL(IF(A1:F12=""Bonsoir"",ROW(A1:F12)),1),),0))")

on est bien d'accord que l'on peut appeler ca "RECHERCHEX"

j'avais trouvé une formule sur ce forum un peu plus simple qui renvoyait la valeur si elle était trouvée avec dans cette formule une chose du genre
......9^9Feuil1!A1:F12.....
mais je ne trouve plus la discussion car je ne me souvient plus du titre

 

[job75]

Bonjour patricktoulon, le fil, le forum,

En VBA on peut comparer ces 2 fonctions :

Function ADRESSE1(P As Range, txt As String)
ADRESSE1 = Evaluate("ADDRESS(SMALL(IF(" & P.Address & "=""" & txt & """,ROW(" & P.Address & ")),1),MATCH(""" & txt & """,INDEX(" & P.Address & ",SMALL(IF(" & P.Address & "=""" & txt & """,ROW(" & P.Address & ")),1),),0))")
End Function

Function ADRESSE2(P As Range, txt As String)
ADRESSE2 = P.Find(txt, , xlValues, xlWhole, xlByRows).Address
End Function

Bonne journée.

A+

 

[job75]

J'ai comparé les durées du recalcul de 10 000 fonctions en les rendant volatiles :

- ADRESSE1 => 2,8 secondes

- ADRESSE2 => 18,9 secondes.

On le sait la méthode Find prend beaucoup de temps.

 

[patricktoulon]

donc au final c'est une bonne chose non d'utiliser la formule en vba non ?

 

[job75]

Oui s'il y a un grand nombre de fonctions à calculer.

 

[job75]

Bon sur les 2 fichiers joints je constate que ADRESSE2 est plus rapide que ADRESSE1...

 

[patricktoulon]

bonjour job75
c'est pas tout a fait comme ça que j'entendais l'utiliser
plutôt comme ça mais j'ai pas un résultat cohérent
les colonnes sont mal identifiées dans les adresses du résultat

Sub
Sub test()
    MsgBox recherchex([A1:J300], "toto")
End Sub

Function recherchex(p As Range, txt As String)
    Dim x$, z$, cel1 As Range,fin as boolean
    Set cel1 = p.Cells(1)
    Do
        Set p = Range(cel1, p.Cells(p.Cells.Count))
        On Error Resume Next
         x = Evaluate("ADDRESS(SMALL(IF(" & p.Address & "=""" & txt & """,ROW(" & p.Address & ")),1),MATCH(""" & txt & """,INDEX(" & p.Address & ",SMALL(IF(" & p.Address & "=""" & txt & """,ROW(" & p.Address & ")),1),),0))")
        fin = Err.Number > 0: Err.Clear
       z = z & " " & x
         Set cel1 = Cells(Range(x).Row + 1, "A")
    Debug.Print "trouvée! " & x & "  reste a chercher " & p.Address
       Loop Until fin = True
    recherchex = Range(Replace(Application.Trim(z), " ", ",")).Address
End Function