VBA suppression conditionnelle de valeur

Version imprimable

13/06/2012, 10h40
jle41

1 pièce(s) jointe(s)

VBA suppression conditionnelle de valeur

Bonjour à tous,

Je suis nouveau sur le forum et débutant en VBA, merci d'avance pour toutes vos réponses et le temps que vous consacrerez à m'aider.
Je précise que je suis sur Excel 2003.

Je suis stagiaire et j'ai un gros travail à faire à partir d'un tableau croisé dynamique qui prend ça source à partir de la base de données des employés de l'entreprise, qui comprend environ 1500 lignes (une par poste).
Je cherche depuis quelques jours une macro me permettant de gérer au mieux les doublons. Je m'explique :
dans ma base de données des salariés, on trouve entre autre leur code poste, leur date d'arrivée dans ce poste ainsi que leur type de contrat CDI ou CDD.

A B C D
Untel1 01/02/2007 P004 CDI

Le problème est que plusieurs personnes peuvent avoir le même code poste (en cas de remplacement par exemple). Or, ce poste sera compté 2 fois dans mon tableau croisé alors qu'il ne représente qu'un poste en réalité.

Donc, je cherche une macro conditionnelle me permettant de supprimer le code poste en cas de doublons : - si le contrat du doublon est un CDD, alors son Code poste =""
- si le contrat du doublon est un CDI, il faudra que le code poste qui possède la date la plus récente soit =""
Ainsi, dans mon exemple, pour le code P004, les codes poste de Untel7 (car CDD) et Untel4 (CDI et code poste le plus récent) devront être supprimés, tout comme celui de Untel8 pour I002, car en CDD.

J'espère avoir été clair, mais j'en doute... J'ai commencé à bidouiller des codes, je peux vous les indiquer si cela peut vous aider mais je ne pense pas.

Merci beaucoup pour votre aide.
13/06/2012, 11h24
ZebreLoup

J'ai fait un truc tout bête dans ton fichier, j'ai rajouter en F2 la formule suivante que j'ai copiée sur toute la colonne :

Code:

=SOMMEPROD(1*($D$2:$D$11=D2)*($C$2:$C$11>C2))

Il faut ensuite enlever toutes les lignes pour lesquelles les formules ne donnent pas 0. (Il faut bien modifier le 11 en mettant toute la plage de ton vrai tableau)

Bonjour ZebreLoup, merci beaucoup pour ton aide et la réponse!

A propos de ta formule, je n'avais pas du tout pensé à utiliser sommeprod, je m'étais de suite lancé en VBA (sans succès bien sûr). L'idée est je pense très bonne mais elle ne prend pas en compte le type de contrat CDI ou CDD. Par exemple pour Untel8, il devrait être différent de 0 puisqu'il est en CDD.
Je pense qu'il y a juste à rajouter une petite condition, là pour l'instant je vais manger :D , je teste donc tout ça par la suite.

Si ça peut aider, je montre le code VBA que j'avasi trouvé sur un forum et adapté à ma sauce :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sub Essai() For i = 2 To 2500 For j = 3 To 2500 If Cells("S" & i).Value = Range("S" & j) Then If Range("O" & i).Value = "DI" Then If Range("N" & i) > Range("N" & j) Then Range("S" & i).Value = "" End If End If If Range("O" & i).Value = "DD" Then Range("S" & i).Value = "" End If End If Next j Next i End Sub
Bien sûr il ne fonctionne pas... De plus j'ai peur que ce type de code prenne un temps monstre à analyser tout le fichier (environ 1500 lignes). Idéalement j'aimerais utiliser un code vba afin d'automatiser tout ça mais si une formule telle que tu m'as donnée fonctionne, ça serait vraiment bien!

Je me penche dessus tout à l'heure, en tout cas merci beaucoup pour l'aide!

13/06/2012, 13h15
ZebreLoup

Effectivement, il suffirait de rajouter une (ou deux) conditions, mais je n'ai pas compris exactement le cas particulier du CDD.
Pour ton code, effectivement la double boucle risque de ne pas être optimale. Il y aurait d'autres solutions : utiliser un dictionnaire, trier d'abord les données puis utiliser un code plus simple et optimisé...

Bonjour,

Voici un code VBA qui reprend l’idée de ZebreLoup (excepté l’utilisation d’une collection à la place d’un dictionnaire).
On trie les données sur la date d'arrivée dans le poste afin de pouvoir isoler plus facilement la date d'arrivée la plus ancienne ; on utilise une collection afin d'obtenir une liste sans doublon ; et enfin, on compare les éléments de la collection aux codes de la liste pour effectuer la suppression conditionnelle de valeur.

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
Option Explicit
Sub Test()
Dim Cel As Range, Plage As Range
Dim C As Collection
Dim DerLig As Long, i As Long, j As Long, Cptr As Long
    Application.ScreenUpdating = False
    Set C = New Collection
    On Error Resume Next
    With Worksheets("Feuil1")
        DerLig = .Range("D" & .Rows.Count).End(xlUp).Row
 
        'Tri ascendant sur la date d'arrivée dans le poste
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("C2:C" & DerLig) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:E" & DerLig)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
 
        'Les codes poste sont placés dans une collection afin d'obtenir une liste sans doublon
        Set Plage = .Range("D2:D" & DerLig)
        For Each Cel In Plage
            If Cel <> "" Then C.Add Cel, CStr(Cel)
        Next Cel
        On Error GoTo 0
 
        'On boucle sur chaque élément de la collection que l'on compare aux codes de la liste.
        For i = 1 To C.Count
        Cptr = 0
            For j = 1 To DerLig
                If .Range("D" & j).Value = C.Item(i) Then
                    If Cptr > 0 Then .Range("D" & j) = ""
                    Cptr = Cptr + 1
                End If
            Next j
        Next i
 
        'Tri ascendant sur le nom (la liste est replacée dans l'ordre initial)
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A" & DerLig) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:E" & DerLig)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
 
    End With
    Set Plage = Nothing
    Set C = Nothing
    Application.ScreenUpdating = True
End Sub

Cordialement.

13/06/2012, 14h52
jle41

1 pièce(s) jointe(s)

Bon j'ai regardé un peu ça. En fait c'est tout bête mais pas facile à exprimer! En cas de doublons avec un CDD (si un CDD a le même code poste qu'un CDI), on supprime son code poste (et surtout pas la ligne entière).
En cas de doublons de code poste entre 2 employés en CDI, on supprime le code poste le plus ancien.
Je crois bien que ta formule était top, sauf pour les CDD.
Du coup j'ai adapté un peu, ça me fait du bricolage pas terrible mais j'ai l'impression que ça fonctionne.. à vérifier sur mon fichier original!

Je te joints le résultat à partir de ta formule si ça t'interesse

En tout cas, merci vraiment, j'avais foncé tête baissé dans du code que je ne maîtrise pas donc...
Mais si quelqu'un maîtrise et les compétences pour programmer tout ça et ainsi rendre le fichier plus propre, je suis toujours preneur!

Merci encore Zebreloup
13/06/2012, 15h03
jle41

J'ai oublié de préciser : dans le dernier fichier joint, il faudra que je m'occupe uniquement des code poste = 1 (et non 0 ou 2).
En fait je ne supprime pas les autres car j'ai quand même besoin des noms dans le tableau croisé

bonjour gFZT82

je viens de voir la réponse, je regarde également ta solution.
Un grand merci!

Alors par contre pour la ligne :

Code:

SortOn:=xlSortOnValues

ça me met

Citation:

erreur de compilation : variable non définie

Mais j'ai l'impression que l'idée y est...
Merci
13/06/2012, 16h30
gFZT82
Effectivement, mon code fonctionne avec Excel 2007.
Avec Excel 2003, tu devrais avoir un tri qui ressemble à ça
Code:

1 2 3 'Tri ascendant sur la date d'arrivée dans le poste .Range("A1:E" & DerLig).Sort Key1:=.Range("C1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False
et même principe pour refaire le tri (si nécessaire) sur le nom en fin de procédure.

Si besoin, tu peux vérifier le code avec l'enregistreur de macro (tri ascendant sur la colonne C).

Cordialement.
14/06/2012, 09h43
jle41

Bonjour gFZT82

J'ai fait la modif et testé ton code sur l'exemple, c'est exactement ce que je cherchais!

Merci beaucoup, à une prochaine sur le forum peut être.

Cordialement