VBA : recherche d'une valeur immédiatement sup. dans une colonne de donnée

Le 23/01/2018 à 14:51

Voila j'ai réussi a faire une chose en Excel basique mais je n'arrive pas à la refaire en VBA en fait je vois pas trop comment ca serait faisable sous VBA

On a une colonne de donnée avec des chiffres et on cherche les bornes immédiatement supérieures et inférieures à A, donc on saisit la valeur numérique A on récupére ces 2 valeurs encadrant A

Voila la formule magique sous excel "basique"

Borne inférieure

= INDEX($B$13:$B$23;RANG(RECHERCHE(C2;$B$13:$B$23);$B$13:$B$23;1))

Borne supérieure

= INDEX($B$13:$B$23;RANG(RECHERCHE(C2;$B$13:$B$23);$B$13:$B$23;1)+1)

Quelle est la solution en VBA ?

Merci

Voir pièce jointe pour l'illustration

borne
Le 23/01/2018 à 15:09

Bonjour,

Sub Test()

    With Application

        MsgBox .Index(Range("B13:B23"), .Rank(.Lookup(Range("C2"), Range("B13:B23")), Range("B13:B23"), 1))
        MsgBox .Index(Range("B13:B23"), .Rank(.Lookup(Range("C2"), Range("B13:B23")), Range("B13:B23"), 1) + 1)

    End With

End Sub
Le 26/01/2018 à 13:30

Merci mais si je veux pouvoir réutiliser cette valeur pour la suite du programme et pas seulement avoir le résultat brute ? Si je veux que dans la suite de mon programme cette valeur soit attribuée à la variable "SA2"

Et en fait j'aimerais indiquer la selection dans le genre :

Index(Sheets("Source").Columns(5), .Rank(.Lookup(Sheets("Source").Cells(9, 6).Value), Sheets("Source").Columns(5)), Sheets("Source").Columns(5), 1) = SA2

Sheets("conso").Cells(3, 9).Value = SA2

mais ca renvoie aucune valeur

Le 26/01/2018 à 16:21

Bonjour,

De cette façon alors :

Sub Test()

    Dim Maxi As Double
    Dim Mini As Double

    With Application

        Mini = .Index(Range("B13:B23"), .Rank(.Lookup(Range("C2"), Range("B13:B23")), Range("B13:B23"), 1))
        Maxi = .Index(Range("B13:B23"), .Rank(.Lookup(Range("C2"), Range("B13:B23")), Range("B13:B23"), 1) + 1)

    End With

    'pour le test, à supprimer !
    MsgBox "La valeur imédiatement en dessous de '" & Range("C2").Value & "' est '" & Mini & "' !" _
           & vbCrLf _
           & "La valeur imédiatement en dessus de '" & Range("C2").Value & "' est '" & Maxi & "' !"

    'résultats dans les cellules I3 et I4 de la feuille "conso"
    Sheets("conso").Cells(3, 9).Value = Mini
    Sheets("conso").Cells(4, 9).Value = Maxi

End Sub

ça par contre ça ne fonctionne pas :

Index(Sheets("Source").Columns(5), .Rank(.Lookup(Sheets("Source").Cells(9, 6).Value), Sheets("Source").Columns(5)), Sheets("Source").Columns(5), 1) = SA2

on affecte pas une valeur à une formule, c'est elle qui retourne une valeur !

Le 26/01/2018 à 17:31

Si je comprends bien l'équivalent VBA et excel donne ceci (il faut chercher les noms anglais des fonctions pour VBA) :

.Index( Range("B13:B23") , .Rank(.Lookup( Range("C2") , Range("B13:B23") ) , Range("B13:B23") , 1 ) + 1 )

INDEX( $B$13:$B$23 ; RANG(RECHERCHE( C2 ; $B$13:$B$23 ) ; $B$13:$B$23 ; 1 ) +1 )

Est ce qu'on peut mettre des couleurs dans les commentaires de l'éditeur VBA car au bout d'un moment je m'y retrouve plus dans l'ouverture fermeture des parenthèse ni dans la limite de chaque formule car la on a de INDEX + RANG + RECHERCHE + chacun une liste d'argument ... bref ca devient compliqué de s'y retrouver

On est pas obligé de mettre "Application.WorksheetFunction" devant chaque fonction ? (Application.WorksheetFunction.Lookup, Application.WorksheetFunction.rank, Application.WorksheetFunction.index)

Pour ce qui est de mieux s'y retrouver est ce qu'il y a autre chose que Range ? et on peut affecter Range à une valeur ? genre que Range("B13:B23) soit affectée à une varibale "COL1" afin d'alléger le texte

.Index(COL1,.Rank(.Lookup(Range("C2"), COL1), COL1, 1) + 1 ) ?

Je comprends pas trop comment en écrivant .Index(Range("B13:B23") Excel peut savoir de quelle feuille il s'agit ? si j'ai 3 feuilles de calcul dans mon fichier, dont une qui a les "données brutes", une autre le tableau de saisie "utilisateur", et enfin une dernière ou mettre le résultat de la macro, comment excel sait de quelle feuille il s'agit on parle quand on lui dit juste Range("B13:B33") ?

Merci

Le 26/01/2018 à 18:50

Re,

...il faut chercher les noms anglais des fonctions pour VBA

une méthode très simple pour ne pas avoir à chercher l'équivalent en Anglais, après avoir sélectionné la cellule qui comporte la formule à traduire, aller dans la fenêtre d'exécution (Ctrl+G) et taper la ligne de code suivante :

?activecell.Formula

puis touche Entrée, la traduction s'affichera en dessous et il ne restera plus qu'à la copier mais attention, pour les plages il faut passer des Range et non des String

Est ce qu'on peut mettre des couleurs dans les commentaires de l'éditeur VBA car au bout d'un moment je m'y retrouve plus dans l'ouverture fermeture des parenthèse ni dans la limite de chaque formule car la on a de INDEX + RANG + RECHERCHE + chacun une liste d'argument ... bref ca devient compliqué de s'y retrouver

et bien non mais quand tu en oublis une ou que tu en mets une en trop, le compilateur couine et place le curseur où ça coince !

On est pas obligé de mettre "WorksheetFunction" du moment que le parent est "Application"

Pour ce qui est de mieux s'y retrouver est ce qu'il y a autre chose que Range ? et on peut affecter Range à une valeur ? genre que Range("B13:B23) soit affectée à une varibale "COL1" afin d'alléger le texte

.Index(COL1,.Rank(.Lookup(Range("C2"), COL1), COL1, 1) + 1 ) ?

tu peux tout a fait utiliser des variables objet comme ceci par exemple :

Sub Test()

    Dim Plg As Range
    Dim Cel As Range
    Dim Maxi As Double
    Dim Mini As Double

    Set Plg = Range("B13:B23")
    Set Cel = Range("C2")

    With Application

        Mini = .Index(Plg, .Rank(.Lookup(Cel, Plg), Plg, 1))
        Maxi = .Index(Plg, .Rank(.Lookup(Cel, Plg), Plg, 1) + 1)

    End With

    'pour le test, à supprimer !
    MsgBox "La valeur imédiatement en dessous de '" & Cel.Value & "' est '" & Mini & "' !" _
           & vbCrLf _
           & "La valeur imédiatement en dessus de '" & Cel.Value & "' est '" & Maxi & "' !"

End Sub

ne pas oublier d'utiliser l'instruction "Set" pour les variables objet

Je comprends pas trop comment en écrivant .Index(Range("B13:B23") Excel peut savoir de quelle feuille il s'agit ? si j'ai 3 feuilles de calcul dans mon fichier, dont une qui a les "données brutes", une autre le tableau de saisie "utilisateur", et enfin une dernière ou mettre le résultat de la macro, comment excel sait de quelle feuille il s'agit on parle quand on lui dit juste Range("B13:B33") ?

Par défaut, le compilateur utilise la feuille active, si tu actives une autre feuille et que tu lance la proc, tu auras une erreur. Pour éviter ça, il faut "parenter" les Range comme ceci :

Sub Test()

    Dim Plg As Range
    Dim Cel As Range
    Dim Maxi As Double
    Dim Mini As Double

    Set Plg = Worksheets("Feuil1").Range("B13:B23")
    Set Cel = Worksheets("Feuil1").Range("C2")

    With Application

        Mini = .Index(Plg, .Rank(.Lookup(Cel, Plg), Plg, 1))
        Maxi = .Index(Plg, .Rank(.Lookup(Cel, Plg), Plg, 1) + 1)

    End With

    'pour le test, à supprimer !
    MsgBox "La valeur imédiatement en dessous de '" & Cel.Value & "' est '" & Mini & "' !" _
           & vbCrLf _
           & "La valeur imédiatement en dessus de '" & Cel.Value & "' est '" & Maxi & "' !"

End Sub

après ceci, le compilateur sait exactement à quelle feuille appartiennent les plages

Le 29/01/2018 à 13:57

Bon j'ai encore des erreurs et j'en comprends pas trop la source je joints le fichier excel

43tabulation.xlsm (45.35 Ko)
Le 29/01/2018 à 16:40

Bonjour,

Là, tu aurais pu trouver tout seul !

Supprimes les lignes vides dans la colonne E car les fonctions de recherche n'aiment pas trop les trous dans les plages !

Pour gérer l'erreur dans le code :

With Application

    On Error Resume Next
    SA1 = .Index(Plg, .Rank(.Lookup(PK, Plg), Plg, 1))
    SA2 = .Index(Plg, .Rank(.Lookup(PK, Plg), Plg, 1) + 1)

    If Err.Number <> 0 Then

        MsgBox "Erreur !" & vbCrLf & "La formule ne retourne pas de valeur valide (de type 'Double') !"
        Exit Sub

    End If

End With
Le 29/01/2018 à 17:01

Re,

Si tu ne veux ou peux pas supprimer ces lignes, utilises plutôt une boucle !

remplace les lignes de code que je t'ai posté précédemment :

Dim Cel As Range
Dim Valeur

For Each Cel In Plg

    If IsNumeric(Cel.Value) And Cel.Value > PK.Value Then

        SA1 = Valeur
        SA2 = Cel.Value
        Exit For

    End If

    If IsNumeric(Cel.Value) Then Valeur = Cel.Value

Next Cel
Le 29/01/2018 à 17:27

merci ! Bon j'ai un peu de mal à comprendre au début mais en cherchant on comprend la logique, voila le code qui fonctionne enfin !

Bon j'ai un autre souci mais c'est un autre sujet (si on veut)

Le 30/01/2018 à 06:52

Bonjour,

Je comprends pas pourquoi on a 2 If et un seul endif dans le code précédent (meme si ca fonctionne)

il y a maintes façon d'écrire le code et la dans une instruction If Then Else End If, tu peux écrire l'instruction à réaliser sur la même ligne que le test de condition comme sur cette ligne de code :

If IsNumeric(cel.Value) Then valeur = cel.Value: Pos1 = cel.Address(0, 0)

ici, il y a deux instructions qui se suivent mais dans ce cas, deux points les sépare ce qui évite de mettre la ligne au dessous

Maintenant cela me donne la borne inférieure immédiate mais comment obtenir la borne supérieure immédiate ? Comment récupérer la position de cette borne inférieure dans mon tableau (son "range") ?

dans le code précédent :

 SA1 = Valeur
 SA2 = Cel.Value
 Exit For

les deux valeurs étaient récupérées je ne vois pas pourquoi tu en as fais sauter une !

ci-dessous, le code retourne aussi l'adresse de la cellule :

Sub Test()

    Dim Plg As Range
    Dim cel As Range
    Dim PK As Range
    Dim SA1 As Double
    Dim SA2 As Double
    Dim valeur
    Dim Pos1 As String
    Dim Pos2 As String

    Set Plg = Worksheets("Axe En Plan").Range("E10:E102")
    Set PK = Worksheets("Source").Range("F9")

    For Each cel In Plg

        If IsNumeric(cel.Value) And cel.Value > PK.Value Then

            SA1 = valeur
            SA2 = cel.Value
            Pos2 = cel.Address(0, 0)
            Exit For

        End If

        If IsNumeric(cel.Value) Then valeur = cel.Value: Pos1 = cel.Address(0, 0)

    Next cel

    MsgBox "Valeur borne inférieure : " & SA1 & " située dans la cellule " & Pos1 & _
           vbCrLf & _
           "Valeur borne supérieure : " & SA2 & " située dans la cellule " & Pos2

End Sub

PS : quand tu écris ton code, indentes-le il sera plus lisible et quand tu postes du code, place-le entre les balises CODE (bouton </>)

Rechercher des sujets similaires à "vba recherche valeur immediatement sup colonne donnee"