tester et récupérer numero de colonne

Version imprimable

27/07/2009, 15h45
kokoVBA

tester et récupérer numero de colonne

Bonjour,
j'ai la question suivante :
j'ai dans un nombre C de colonnes et un nombre l de lignes des horaires.
Je voudrais récupérer pour chaque ligne le numero de colonne dans lequel je trouve l'horaire le plus tardif de chaque ligne.
J'étais parti sur une méthode avec find mais ça ne marche pas puisque ça me renvoie le max mais pas le n° de colonne.
Merci d'avance pour votre aide.

Bonjour,

Voici ce que je te proposerais

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub TestMaxParligne()
    Dim C As Integer
    Dim L As Integer
    Dim Max As Integer
    Dim LMax As Integer
    Dim sh As Worksheet
 
    Set sh = ActiveSheet
 
    For L = 1 To 10
 
        Max = sh.Cells(L, 1).Value
        LMax = 1
 
        For C = 2 To 10
 
            If Max < sh.Cells(L, C) Then
                Max = sh.Cells(L, C)
                LMax = C - 1
            End If
 
        Next C
 
        sh.Cells(L, 11) = "Maximum " & Max & " à la colonne " & LMax & "."
 
    Next L
 
End Sub

Regarde tout ça, si tu as besoin d'explication n'hésite pas!

P.S. N'oublies pas de changer le départ et la fin de tes boulces et de vérifier ce que tu fais avec le numéro de colonne.

27/07/2009, 17h10
kokoVBA

Bonjour Drakkar_agfa,
merci pour ta réponse, néanmoins elle ne résoud pas mon problème :
il faut considérer que je dois comparer des dates (ou plutôt des heures de passage), et l'utilisation du suffixe

Code:

.value

ne marche donc pas.
27/07/2009, 17h21
drakkar_agfa

D'accord,

peux-tu nous donner un exemple de date et/ou heure à comparer? Est-ce du texte? un format de date? du numérique?

Cela changera notre méthode de comparaison (le if Max < ... )
27/07/2009, 17h22
Djromé

Réponse à KokoVba

Bonjour,

Essaie,

Code:

.value2

il me semble que c'est prévu pour les date.

Salutations:ccool:
27/07/2009, 17h26
kokoVBA

1 pièce(s) jointe(s)

Pour éclairer mon problème je vous mets un fichier en PJ. Dans mon exemple, comment :
- faire pour qu'entre la ligne 9 et la ligne 61, il me renvoie l'adresse du min et du max des colonnes 4 à 19?

Mon objectif à terme est de remplir la colonne 24 de la façon suivante :
- je prends la 1ère ligne, je regarde le max et la parite (colonne 1)
- je descend d'un cran et si :
- la différence de temps entre mon max et la valeur > 20 mn
et - la parite est différente (ou bien la valeur est le min de sa ligne)
alors la valeur en colonne 24 est la valeur précédement remplie +1
on voit que cette logique est remplie en ligne 11 puis en ligne 25.

Voilà l'idée, n'hésitez pas à me poser plus de questions si besoin est. Je pense qu'il faudra que j'utilise plusieurs boucles mais pour l'instant il faut déjà que je récupère les colonnes où sont les max qui m'intéressent.

J'ai adapté mon code pour qu'il fonctionne avec ta feuille.
Voici le code que tu peux copier et utiliser sur ta feuille.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub TestMaxParligne()
    Dim C As Integer
    Dim L As Integer
    Dim Max As Variant
    Dim LMax As Integer
    Dim sh As Worksheet
 
    Set sh = ActiveSheet
 
    For L = 9 To 61
 
        Max = sh.Cells(L, 4).Value2
        LMax = 4
 
        For C = 4 To 19
 
            If Max < sh.Cells(L, C).Value2 Then
                Max = sh.Cells(L, C)
                LMax = C - 1
            End If
 
        Next C
 
        sh.Cells(L, 20) = "Maximum " & Max & " à la colonne " & LMax & "."
 
    Next L
 
End Sub

Tu pourras ainsi avoir la colonne avec le max. Je te laisse commencer la deuxième partie de ton code. Je n'ai pas le temps pour l'instant de tout faire ça mais si tu as des questions, n'hésite pas, tu nous posteras ce que tu auras fait.

27/07/2009, 17h51
kokoVBA

Merci Drakkar_agfa,
ton programme marche mais il y a quelques problèmes :
- sur le format, il transforme mon horaire en nombre décimal
- sur le résultat de la colonne, ça marche bien en ligne 10 mais pas en ligne 9 par exemple où il y a un décalage. Je vais essayer de voir pourquoi.
A+

Edit : sur le 2 ème point il suffit de remplacer :

Code:

LMax = C - 1

par

Code:

LMax = C

Il reste le premier point.
27/07/2009, 17h57
drakkar_agfa

Pour le décalage, voici le problème

Code:

LMax = C - 1

(Juste à enlever le -1 ... je ne sais pas réellement pourquoi j'ai inscrit cela.)

Pour le décimal, c'est pour la comparaison, mais en pratique si tu as besoin de seulement le nurméro de colonne, il n'a pas de réel problème.

EDIT : Grilled, bon, comme je disais, tu peux seulement utiliser LMax sans utiliser le message que la macro écrit dans la colonne 20...

Tu pourrax seulement inscrire LMax dans la colonne et quand tu auras besoin de la valeur max, alors tu trouveras avec sh.cells(L,LMax)

En fait c'est une très bonne chose de récupérer aussi le résultat, ça me servira pour le reste de la macro. Maintenant, ce que je voudrais c'est que ce résultat soit sous le même format que les données, c'est à dire en heures minutes.

Par ailleurs, j'ai essayé de récupérer le min, de la façon suivante (j'ai inséré une colonne supplémentaire entre la 19 et la 20:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sub TestMaxParligne()
    Dim C As Integer
    Dim L As Integer
    Dim Max As Variant
    Dim LMax As Integer
    Dim sh As Worksheet
    Dim Min As Variant
    Dim LMin As Integer
 
 
    Set sh = ActiveSheet
 
    For L = 9 To 61
 
        Max = sh.Cells(L, 4).Value2
        LMax = 4
        Min = sh.Cells(L, 4).Value2
        LMin = 19
        For C = 4 To 19
 
            If Max < sh.Cells(L, C).Value2 Then
                Max = sh.Cells(L, C)
                LMax = C
            End If
 
        Next C
        For C = 19 To 4 Step -1
            If Min > sh.Cells(L, C).Value2 Then
                Min = sh.Cells(L, C)
                LMin = C
            End If
        Next
 
        sh.Cells(L, 21) = Max & " à la colonne " & LMax & "."
        sh.Cells(L, 20) = Min & " à la colonne " & LMin & "."
    Next L
 
End Sub

mais j'ai un problème à la ligne 9 car il les colonnes 4 à 17 étant vides il ne trouve rien...
As-tu une piste pour contourner le problème même quand toute la ligne n'est pas renseignée?
Merci

Pour comparer des dates il est quand même plus évident d'utiliser Datediff:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub Journee()
Dim ligne, colonne As Integer
Dim CMax, Max, compare
 
ligne = 9
While Worksheets("journ").Cells(ligne, 1) <> ""
    If Worksheets("journ").Cells(ligne, 4) = "" Then
        Max = "00:00:00"
    Else
        Max = Format(Worksheets("journ").Cells(ligne, 4), "hh:nn:ss")
    End If
    CMax = 4
    For colonne = 5 To 19
        If Worksheets("journ").Cells(ligne, colonne) = "" Then
            compare = "00:00:00"
        Else
            compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
            If DateDiff("s", Max, compare) > 0 Then
                Max = compare
                CMax = colonne
            End If
        End If
    Next
    If Max = "00:00:00" Then
        Worksheets("journ").Cells(ligne, 20) = "Pas d'horaire"
    Else
        Worksheets("journ").Cells(ligne, 20) = "Maximum " & Max & " à la colonne " & CMax & "."
    End If
    ligne = ligne + 1
Wend
End Sub

27/07/2009, 18h27
kokoVBA

Bonjour Many31,
ta solution marche également, et garde le bon format.
En revanche même question que dans mon post précédent : comment avoir aussi le min, sachant qu'il peut y avoir des cases vides?
Merci

Ce bloque correspond au max
Code:

1 2 3 4 If Max < sh.Cells(L, C).Value2 Then Max = sh.Cells(L, C) LMax = C End If
Tu peux le réutiliser et le modifier avec le Min. En gros :
Code:

1 2 3 4 5 6 if sh.cells(L,C)<>"" then if min > sh.cells(L,C).value then Min = sh.cells(L,C) LMin = C end if end if
P.S. Je m'excuse pour la synthaxe, j'ai écris sans VBA alors les tabulations et tout n'est pas idéal!

Bonjour,
j'ai essayé d'adapter le code de Many31 pour obtenir également le min.
Voici le code en question :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub Journee()
Dim ligne, colonne, lfin, cfin, cref As Integer
Dim CMax, Max, CMin, Min, Tarr, compare
 
 
cfin = InputBox("rentrer le numéro de la dernière colonne de données")
lfin = Cells(65335, 2).End(xlUp).Row
 
ligne = 9
While Worksheets("journ").Cells(ligne, 1) <> ""
    If Worksheets("journ").Cells(ligne, 4) = "" Then
        Max = "00:00:00"
    Else
        Max = Format(Worksheets("journ").Cells(ligne, 4), "hh:nn:ss")
    End If
    If Worksheets("journ").Cells(ligne, cfin) = "" Then
        Min = "24:59:59"
    Else
        Min = Format(Worksheets("journ").Cells(ligne, cfin), "hh:nn:ss")
    End If
    CMax = 4
    CMin = cfin
 
    For colonne = 5 To cfin
        If Worksheets("journ").Cells(ligne, colonne) = "" Then
            compare = "00:00:00"
        Else
            compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
            If DateDiff("s", Max, compare) > 0 Then
                Max = compare
                CMax = colonne
            End If
        End If
    Next
 
    For colonne = cfin - 1 To 4 Step -1
        If Worksheets("journ").Cells(ligne, colonne) = "" Then
                compare = "00:00:00"
    Else
            compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
            If DateDiff("s", Min, compare) < 0 Then
                Min = compare
                CMin = colonne
            End If
        End If
    Next
 
    'Tarr = Max
    'cref = CMax
    'If Min > Tarr + tret Then
    MsgBox Min & "colonne" & CMin
 
    If Max = "00:00:00" Then
        Worksheets("journ").Cells(ligne, 21) = "Pas d'horaire"
    Else
        Worksheets("journ").Cells(ligne, 21) = Max
        Worksheets("journ").Cells(ligne, 20) = CMax
    End If
    ligne = ligne + 1
Wend
End Sub

qui ne marche pas. Le code bloque à la ligne

Code:

If Worksheets("journ").Cells(ligne, cfin) = "" Then

Merci pour votre aide.

Edit : je viens de remplacer cfin par 19, et ça marche. C'est donc qu'il ne reconnait pas cfin comme un integer comme il le devrait. Quelqu'un a-t-il une piste pour améliorer la syntaxe? Merci

Tu as une erreur détecté sur l'heure 24:59:59 qui n'existe pas

Voici le code pour le min et le max
le cfin est utilisé comme paramètre d'entré. Pour le nombre de ligne, le code se base sur la première cellule de chaque ligne. Si celle si est vide il s'arrête (While Worksheets("journ").Cells(ligne, 1) <> "")

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Option Explicit
 
Sub Journee(cfin As Integer)
Dim ligne, colonne As Integer
Dim CMax, Max, CMin, Min, Tarr, compare
 
ligne = 9
While Worksheets("journ").Cells(ligne, 1) <> ""
    If Worksheets("journ").Cells(ligne, 4) = "" Then
        Max = "00:00:00"
        Min = "23:59:59"
    Else
        Max = Format(Worksheets("journ").Cells(ligne, 4), "hh:nn:ss")
        Min = Max
    End If
 
    CMax = 4
    CMin = CMax
 
    For colonne = 5 To cfin
        If Worksheets("journ").Cells(ligne, colonne) <> "" Then
            compare = Format(Worksheets("journ").Cells(ligne, colonne), "hh:nn:ss")
            If DateDiff("s", Max, compare) > 0 Then
                Max = compare
                CMax = colonne
            End If
            If DateDiff("s", Min, compare) < 0 Then
                Min = compare
                CMin = colonne
            End If
        End If
    Next
 
    If Max = "00:00:00" Then
        Worksheets("journ").Cells(ligne, cfin + 1) = "Pas d'horaire"
    Else
        Worksheets("journ").Cells(ligne, cfin + 2) = Max
        Worksheets("journ").Cells(ligne, cfin + 1) = CMax
        Worksheets("journ").Cells(ligne, cfin + 4) = Min
        Worksheets("journ").Cells(ligne, cfin + 3) = CMin
    End If
    ligne = ligne + 1
Wend
End Sub
 
Sub test()
Call Journee(19)
End Sub

Sinon pour ton autre question (sur le cfin)
tu peux changer le format en integer:

Code:

cfin = CInt(InputBox("rentrer le numéro de la dernière colonne de données"))

28/07/2009, 10h50
kokoVBA

Merci Many31, effectivement ça marche.

J'en profite pour te poser une autre question :
je voudrais automatiser la récupération de cfin, pour qu'on ait pas besoin de le demander à l'utilisateur. J'avais pensé à quelque chose du style :

Code:

cfin = Cells(7, 256).End(xlLeft).column

pour récupérer la dernière colonne non vide. Mais ça ne marche pas.
Y a-t-il là aussi une subtilité d'écriture VBA qui m'échappe?
Merci
28/07/2009, 11h03
Many31
Le End va te donner la dernière cellule non vide :/

a voir l'exemple que tu nous a donné et à titre personnel (moi j'adore les While...) je testerai la couleur de fond de tes colonnes ou une autre valeur commune (présence du P par exemple dans la ligne 8 etc.)

en testant la couleur ca peut donner ca
Code:

1 2 3 4 5 6 7 8 9 10 Sub test() Dim f As Integer f = 4 While Worksheets("journ").Cells(8, f).Interior.ColorIndex = 35 f = f + 1 Wend Call Journee(f - 1) End Sub
Tu peux aussi tester la présence ou non d'un x (While Worksheets("journ").Cells(8, f) <> x) il faut juste que tu trouves ce qui va être commun dans tous les cas :P (couleur, valeur etc.)
28/07/2009, 11h52
kokoVBA

Alors là c'est pas trop clair pour moi.
:calim2:
Je crois que je vais rester sur un inputbox.
Ce qui m'étonne c'est qu'on ne puisse pas faire

Code:

cfin = Cells(7, 256).End(xlLeft).column

alors que l'on fait fréquemment

Code:

Cells(65536, x).End(xlUp).Row

pour trouver la dernière ligne non vide.
28/07/2009, 11h56
Many31

j'avais pas compris ta question...

C'est pas XlLeft qu'il faut utiliser mais xlToLeft ou xlToRight
28/07/2009, 14h07
kokoVBA

Mille merci!
:ccool:
c'est donc :resolu: