Récupérer le numéro d'une ligne [Résolu]

[Vincent32]

Chers tous,

Je cherche à récupérer le numéro de la ligne qui est le plus proche de ma valeur (ici la valeur est fixée à 3000).

Voic le code que j'ai écris et qui ne fonctionne pas, pourquoi, quelle(s) solution(s) ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Sub valeur ()
    Dim ws1 As Worksheet
    Dim Count As Long
    Dim Lig1 As Long
    Dim PremL As Long
    Dim A As Long, B As Long, C As Long
    Set ws1 = ThisWorkbook.Worksheets("Feuille de données du graphique")
 
    PremL = ws1.[A1]
    Lig1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row 'Recherche la dernière ligne contenant une donnée
 
    For Count = PremL To Lig1
        A = 3000 - ws1.Cells(Count, 1)
        B = 3000 - ws1.Cells(Count + 1, 1)
        If Abs(A) < Abs(B) Then
        C = A.Row
        End If
    Next
        MsgBox C
 
end sub

Merci pour votre aide

[Michel_M]

Bonjour

sans t'occuper de la dernière ligne (puisque tu cherche la dernière dans la colonne A, il n'y a rien en dessous et donc, on peut prendre un nombre de ligne supérieur)
en utilisant une formule matricielle dans VBA
essaies

Code :

1
2
3
Sub valeur_proche()
MsgBox [Match(MIN(ABS(3000-(A1:A1000))),ABS(3000-A1:A1000),0)]
End Sub

Si ta valeur est dans une cellule B1 par exemple remplace 3000 par B1

Code :

1
2
3
Sub valeur_proche()
MsgBox [Match(MIN(ABS(B1-(A1:A1000))),ABS(B1-A1:A1000),0)]
End Sub

[Vincent32]

Bonjour,

La solution proposée ne fonctionne pas.

Pour info la valeur retournée doit correspondre au numéro de la ligne où est situé la valeur la plus proches de celle désirée (les 3000 dans mon cas).

La msgBox n'est là qu'à titre indicatif. La valeur retournée dans C doit me permettre de réaliser d'autres calculs.

[jfontaine]

Bonjour,

En passant par une formule intermédiaire calculée dans une cellule que l'on pourra masquer ou vider après son utilisation

Code :

1
2
3
4
5
6
7
8
9
10
11
12
Dim dblResultat As Double
Dim Rg As Range
 
'On stock en D1 la valeur la valeur max mais inférieur à 3000
Range("D1").FormulaLocal = "=SI(NB.SI(A1:A1000;""<=3000"")>0;PETITE.VALEUR(A1:A1000;NB.SI(A1:A1000;""<=3000""));0)"
 
Set Rg = Range("A:A").Find(what:=Range("D1").Value, lookat:=xlWhole)
If Not Rg Is Nothing Then
    MsgBox Rg.Row
End If
 
Range("D1").Value =""

[Vincent32]

Je vais être pénible mais je souhaiterais ne pas à avoir à stocker la valeur dans une cellule intermédiaire, si c'est possible.

¨Pourquoi le code suivant ne fonctionne pas ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub valeur ()
    Dim ws1 As Worksheet
    Dim Count As Long
    Dim Lig1 As Long
    Dim PremL As Long
    Dim A As Long, B As Long, C As Long
    Set ws1 = ThisWorkbook.Worksheets("Feuille de données du graphique")
 
    PremL = ws1.[A1]
    Lig1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row 'Recherche la dernière ligne contenant une donnée
 
    For Count = PremL To Lig1
        A = 3000 - ws1.Cells(Count, 1)
        B = 3000 - ws1.Cells(Count + 1, 1)
        If Abs(A) < Abs(B) Then
        C = ws1.Cells(Count,1).Row
        End If
    Next
        MsgBox C
 
end sub

[jfontaine]

¨Pourquoi le code suivant ne fonctionne pas ?

Pour te répondre il faudrait savoir ce qui va pas. Message d'erreur, résultat erroné ...

[Vincent32]

Désolé,

Tout d'abord il me dit incompatibilité de type pour PremL=ws1.[A1]

Ensuite, Erreur définie par l'application ou l'objet => A = 3000 - ws1.Cells(Count, 1)

[Michel_M]

A tout hasard, ma proposition avait été testée et fonctionnait
pour le msg, je me suis basé sur ce que tu exprimais dans ta demande
Désolé de t'avoir dérangé.

[Vincent32]

Bonjour Michel,

Je suppose que mes propos ont été mal interprétés et je m'en excuse.

Désolé de t'avoir dérangé.

J'apprécie énormément l'aide que tu m'apportes

Cependant la ligne de code suivante :

Code :

MsgBox [Match(MIN(ABS(B1-(A1:A1000))),ABS(B1-A1:A1000),0)]

(qui fonctionne parfaitement bien dans ton classeur) ne fonctionne pas dans le mien !

L'erreur renvoyée est la suivante : "Incompatibilité de type" avec ta formule surlignée en jaune !

Je ne comprends pas pourquoi.

Par ailleurs est ce que plutot que MsgBox je peux faire :

Code :

Valeur = Match(MIN(ABS(B1-(A1:A1000))),ABS(B1-A1:A1000),0)

Et ensuite utiliser Valeur pour différents calculs !

Encore une fois désolé si mes propos ont été mal perçus.

Merci beaucoup pour l'aide apporté !

Un grand merci à toi Michel. Ta fiormule fonctionne parfaitement bien

En fait il fallait que je commence à A2 et non A1 car A1 est composé d'un titre et non d'une valeur. Quel étourdis suis-je !!!!

Encore merci pour toute l'aide apportée

Il m'est impossible d'utiliser la valeur retournée par la formule, Pourquoi, comment faire ?

Code :

1
2
3
4
5
6
7
8
9
10
11
'soustraire la ligne 1090 de la feuille 2 à toutes les autres lignes pour renseigner la feuille 3
        ws1.Range("A:A").Copy ws3.Range("A:A") 'Recopie de la colonne A de la feuille ws1 dans la feuille ws3
 
        With ws2
            Val1 = [Match(MIN(ABS(1900-(A2:A65536))),ABS(1900-(A2:A65536)),0)] 'Trouve la ligne la plus proche de 1900
        End With
        For Col = PremC1 To DerC1 - 2 'boucle sur les colonnes
            For Lig = PremL1 To DerL1 'boucle sur les lignes
                ws3.Cells(Lig, Col + 1) = ws2.Cells(Lig, Col + 1) - ws2.Cells(Val1, Col + 1) 'formule soustraction de la ligne la plus proche de 1900 à toutes les autres
            Next Lig      
        Next Col

Erreur : Incompatibilité de type pour la ligne :

Code :

ws3.Cells(Lig, Col + 1) = ws2.Cells(Lig, Col + 1) - ws2.Cells(Val1, Col + 1)

Val1 est déclaré en Long

[jfontaine]

dans un premier, pour éviter les erreurs d’interprétation par VBA

Correspond à l'objet Range

Code :

ws3.Cells(Lig, Col + 1)

Correspond à la valeur de l'objet Range

Code :

ws3.Cells(Lig, Col + 1).Value

Donc

Code :

ws3.Cells(Lig, Col + 1).Value = ws2.Cells(Lig, Col + 1).Value - ws2.Cells(Val1, Col + 1).Value

Quand tu as ce genre d'erreur, mets un point d'arret sur cette ligne et regarde la valeur des variables a ce moment

[Vincent32]

La solution que tu me proposes ne fonctionne pas.J'ai essayé de mettre un point d'arrêt mais je n'arrive pas à cerner ce que ça m'apporte (je ne sais pas m'en servir !)

Merci pour ton aide en tout cas

[jfontaine]

As tu la valeur de Val1 quand ca plante ?

[Vincent32]

non, je vais rajouter un msgBox et je te dis ça

En fait ça donne 1 !!! (ce n'est pas la valeur attendue !)

[gFZT82]

Bonjour,

Dans ta formule

Code :

Val1 = [Match(MIN(ABS(1900-(A2:A65536))),ABS(1900-(A2:A65536)),0)]

Val1 correspond à la position dans la plage A2:A65536.
Pour définir la ligne correspondante, tu dois appliquer un décalage de +1 puisque tu commences en A2.
Ligne = Val1 + 1

Cordialement.

[Vincent32]

Bonjour la solution apportée me permet d'éradiquer le message d'erreur. Cependant :

Code :

Val1 = [Match(MIN(ABS(1900-(A2:A65536))),ABS(1900-(A2:A65536)),0)] 'Trouve la ligne la plus proche de 1900

ne me renvoie pas la bonne ligne : il me renvoie la valeur 1 (ligne 1) alors que la ligne la plus proche de 1900 est la ligne : 1089.

Je suis perdu : tantôt la formule cité précédemment fonctionne (voir le fichier Joint par Michel), Tantôt elle renvoie une valeur "bidon" (dans mon cas !) J'ai esssayé sur un fichier test et le code fonctionne mais là sur ma macro définitive (ça bug)

J'ai beau essayé cette formule, dans un classeur elle me renvoie la bonne valeur, dans l'autre ça me renvoie 1 je n'arrive pas cerner pourquoi !

Je joins mes deux fichiers ainsi que le répertoire nécessaire au chargement des données pour la macro "qui foire"

Merci pour votre aide

[gFZT82]

Bonjour,

Code :

ws3.Cells(Lig, Col + 1).Value = ws2.Cells(Lig, Col + 1).Value - ws2.Cells(ValA + 1, Col + 1).Value

c'est quoi ws2 ?

Cordialement.

[Vincent32]

oui, effectivement dans le code que j'ai fourni je n'ai mi qu'un extrait. En fait ws2 est une worksheet définie dans mon vrai code.

Code :

1
2
3
Dim ws2 as Worksheet
 
set ws2=Sheet("Soustraction")

[gFZT82]

J'ai beau essayé cette formule, dans un classeur elle me renvoie la bonne valeur, dans l'autre ça me renvoie 1 je n'arrive pas cerner pourquoi !

A priori, tu n’es pas positionné sur la bonne feuille.
Fait un essai avec

Code :

1
2
ws1.Select
Val1 = [Match(MIN(ABS(3000-(A2:A65536))),ABS(3000-(A2:A65536)),0)] 'Trouve la ligne la plus proche de 1900

Cordialement.

[Vincent32]

ça fonctionne ! Houra !!!


jfontaine
Michel_M
gFZT82


Merci énormément à vous 3