Discussion :

[mateoa44]

Comme j'ai du utiliser ton code dans 4-5 cas de figure j'ai gardé celui que tu avais mis avant ^^

Mais merci pour la réflexion que tu porte à mon problème !!

J'ai continué comme ça pour la feuille suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
    Dim m As Long 'parcours toutes les lignes
    Dim e As Variant 'stock la valeur de E
    Dim f As Variant 'stock la valeur de K
    Dim g As Variant 'stock la valeur de T
    Dim last1 As Long 'dernière ligne
 
 Sheets("Post it").Select
 
 last1 = Range("K1000000").End(xlUp).Row
 
For m = 3 To last1
    If Not IsError(Range("E" & m).Value) And Not IsError(Range("K" & m).Value) Then
        e = Range("E" & m).Value
        f = Range("K" & m).Value
        If IsNumeric(e) And IsNumeric(f) Then
            If ((e > 2) And (f < 5)) Then Range("K" & m).Interior.ColorIndex = 44
        End If
    End If
 
    If Not IsError(Range("T" & m).Value) And Not IsError(Range("K" & m).Value) Then
        f = Range("K" & m).Value
        g = Range("T" & m).Value
        If IsNumeric(f) And IsNumeric(g) Then
            If (f > (Range("K2"))) And (g > (Range("T2"))) Then Union(Range("K" & m), Range("T" & m)).Interior.ColorIndex = 33
        End If
    End If
Next m

J'ai changé un peu ton code, je ne colorie plus dès le départ (sauf pour la première feuille avec le H/K > 3 mais pour les autres, je colorie seulement celles qui m'intéressent (au lieu de décolorer).

Il faut savoir que c'est une base de données que j'ai moi même constitué, et de ce que j'ai pu comprendre, c'est qu'elle n'aura jamais énormément de lignes, ce n'est pas de la big data !

[patricktoulon]

bonjour a tous
les propositions sont fonctionnelles mais ont quelques lacunnes dans la méthode
ex:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

last1 = Range("K1000000").End(xlUp).Row

perso je ferais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

last1 =cells(rows.count,"K").End(xlUp).Row

après pour les tests if error! ou numérique vous auriez pu simplifier avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if not cells(x,y).value like "[A-Z-a-z,#]" and  cells(x,y).value like "[0-9]" then.....

ou encore le plus simplement du monde si vous avez pas de nombre avec X décimales importantes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if cells(x,y)>0.0000001

ceci réuni donc tout vos test en un seul puisque :
vide =0
avec des lettre =0
erreur =0

bref des solutions il y en a plein
a méditer

et pour résumer mon point de vue
j'aurais fait comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
for each cel in range (cells(1,"k"),cells(rows.count,"k").end(xlup))
if cel >0.0000001 and cel.offset(0,1)>0.0000001
if cel / cel.offset(0,1)>3 then ....
end if 
next

[mateoa44]

Salut Patrick !
Merci pour tes propositions, je vais regarder demain si je peux raccourcir quelques lignes de codes, mais l'essentiel à mes yeux, c'est que ça fonctionne ! haha

Pourrais-tu tout de même expliquer les lignes que tu as écrites s'il te plais ? =p

[jeanmidudu]

@PatrickToulon
Je n'ai pas encore ton avis, je fais avec mes connaissances (je ne suis qu'apprenti en DUT info)
Pour le premier exemple que tu donnes qui pourrait poser problème, je suis d'accord, ça poserait problème si on passait à une version Excel trop ancienne (avant 2010 si je ne m'abuse)

Pour le deuxième je n'y avais absolument pas pensé. Je garde ça en mémoire.

Par contre, en cas de cellule vide (si les options d'Excel ne les considère pas comme 0) alors il manque toujours la vérification

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

cells(x,y)<>""

if cells(x,y)>0.0000001

ceci réuni donc tout vos test en un seul puisque :
vide =0
avec des lettre =0
erreur =0

C'est faux, j'avais essayé pour ma part, et dans chacun des cas précédents j'obtiens une erreur avec le if (avec vide et des lettres j'obtiens l'erreur "argument non valide", et avec #N/A j'obtiens une autre erreur), car j'avais pensé à

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Cells(x,y)>0

(pas besoin des X décimales derrière

[patricktoulon]

oups!! oui autant pour moi j'ai taper selon ma logique sans penser au contraintes excelVBA
en fait il fait il fallait faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if val(cells(x,y))>3 then....

et la on réuni bien tout vos tests

mille excuses

[mateoa44]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if val(cells(x,y))>3 then....

Je ne comprends pas le (x,y) à quoi correspondent-ils ?

[patricktoulon]

bonjour le "x,y" c'est pour l'exemple
pour utiliser cette logique dans ton exercice c'est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
for each cel in range (cells(1,"k"),cells(rows.count,"k").end(xlup))
if val(cel) >0.0000001 and val(cel.offset(0,1))>0.0000001
if cel / cel.offset(0,1)>3 then ....
end if 
next

et si j'ai bien compris ton souhait c'est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if cel / cel.offset(0,1)<3 then Then Union(cel, cel.offset(0,1)).Interior.ColorIndex = 33

[mateoa44]

D'accord, c'est bien ce que je pensais, je te remercie Patrick !!! Je vais essayer ça juste après, si je peux réduire le code c'est toujours cool ! Llà suis repassé sur ma première partie formatage, j'aimerais réduire 4 codes en un seul code.

J'ai 4 feuilles sur mon fichier, je veux appliquer sur 4 feuilles la même mise en forme conditionnelle (pastilles jaunes) sur certaines colonnes (qui ne sont pas les mêmes selon les feuilles).

J'ai fais la technique bidouillage (enregistreur macro) qui fonctionne mais c'est très long comme programme (disons que je reprends 4 fois le même code de la mise en forme) alors que je pense qu'on doit pouvoir le faire en un seul code..

Voici mon code actuel, mais je n'arrive pas à boucler là où il faut !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub CommandButton10_Click()
Dim maxfeuil As Integer, m As Integer
maxfeuil = Sheets.Count - 1 '-1 car en réalité j'ai 5 feuilles, la dernière contient le bouton qui ouvre le userform
For m = 1 To maxfeuil
 Sheets(m).Activate
 
If Sheets("Téléphonie").Select Then
    Range("H:H,L:L,Q:Q").Select
End If
If Sheets("Réclamations").Select Then
    Range("I:I,L:L,X:X").Select
End If
If Sheets("Post it").Select Then
    Range("I:I,L:L,R:R,U:U,X:X").Select
End If
If Sheets("Relation client").Select Then
    Range("I:I,L:L,O:O,T:T").Select
End If
   '                     CODE FOURNI AVEC ENREGISTREUR DE MACRO POUR MISE EN FORME CONDITIONNELLE uniquement sur les valeurs négatives (pastille jaune)  
        Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    End With
 
    Selection.FormatConditions(1).IconCriteria(1).Icon = xlIconYellowCircle
 
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
        .Icon = xlIconYellowCircle
    End With
 
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
        .Icon = xlIconNoCellIcon
 
    End With
Next m
End Sub

Ceci marche très bien mais le "problème" c'est que je dois répéter ce code 4 fois, et au final seul les deux premières lignes changent, je suis sûr qu'on doit pouvoir faire quelque chose comme dans le premier code ! Si vous avez une idée, je suis preneur !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
    Sheets("Téléphonie").Select
    Range("H:H,L:L,Q:Q").Select
 
        Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    End With
 
    Selection.FormatConditions(1).IconCriteria(1).Icon = xlIconYellowCircle
 
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
        .Icon = xlIconYellowCircle
    End With
 
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
        .Icon = xlIconNoCellIcon
 
    End With

[patricktoulon]

re
il faut savoir que l'enregistreur enregistre ce que tu fait donc les select en pagaille que l'on peut absolument supprimer

exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
sheets("truc").activate
range("A10").select
selection.interior.colorindex=33

peut etre remplacer par sans select

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sheet("truc").rabge("A10").interior.colorindex=33

si on par de ce principe
on peut très bien faire tes MFC sans select
il n'est pas nécessaire que le sheets soit actif pour appliquer une MFC

[jeanmidudu]

Re,

Un truc tout bête pour commencer, au lieu d'utiliser plein de IF End If, utilise des ElseIf(ça fait gagner du temps sur le nombre de vérification car si trouve alors ne teste pas les autres). En plus de cela, tu n'es pas obligé d'activer les feuilles pour faire les modifs (perte de temps considérable), teste seulement le nom. Ce qui donne un truc qui ressemble à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
For m = 1 To maxfeuil
With Sheets(m)
If .name = "Téléphonie" Then
    .Range("H:H,L:L,Q:Q").Select
ElseIf .name = "Réclamations" Then
    .Range("I:I,L:L,X:X").Select
ElseIf .name = "Post it" Then
    .Range("I:I,L:L,R:R,U:U,X:X").Select
ElseIf .name = "Relation client" Then
    .Range("I:I,L:L,O:O,T:T").Select
End If
 
'La suite de ton code

Ensuite, fais très attention à bien utiliser la bonne propriété name de tes feuilles. Et je te conseille fortement de retirer les caractères spéciaux dans les propriétés et nom d'objets (retirer les accents si possible)

Evite également au maximum de faire des select, ça prend du temps alors que souvent on peut accéder aux infos sans utiliser Select

Pour la mise en forme, que souhaites-tu faire exactement? Car tu peux toujours utiliser la mise en forme conditionnelle d'Excel (Ce que tu aurais pu faire d'ailleurs dès le tout début, sans passer par VBA, mais je n'y avais pas pensé)

[patricktoulon]

re

Pour la mise en forme, que souhaites-tu faire exactement? Car tu peux toujours utiliser la mise en forme conditionnelle d'Excel (Ce que tu aurais pu faire d'ailleurs dès le tout début, sans passer par VBA, mais je n'y avais pas pensé)

en effet il aurait pu le faire même pour le <3 mais attention jean les MFC sont très! très lourdes en terme de poids de fichiers
la colorations par vba sera de toute façon certainement moins lourdes je le sais par expérience surtout avec des jeux d'icons

après je pense pas qu'il soit nécessaire de faire des mfc dynamique (déclenchée par un évènement bouton )

le faire une fois et c'est tout car je crois qu'il faut supprimer la précédente pour pouvoir appliquer la nouvelle donc plusieurs clicks sur le bouton peuvent déclencher une erreur mais je n'en suis pas sur
comme les mfc sont très lourdes je m'en sert que très rarement

[mateoa44]

Merci pour vos réponses, je vais aller voir ça.

Je vais vous expliquer un peu mon travail, si cela vous intéresse pour que vous puissiez comprendre les objectifs de mon travail.

En fait le fichier actuel, que j'automatise sera dupliqué à chaque trimestre. Il comprend 4 feuilles de données, dans chaque feuille, on a un numéro de groupe, il y a 16 groupes. Chaque groupe est décomposé en plusieurs points de ventes (entre 15 et 25 par groupe), les feuilles contiennent donc aussi le numéro du point de vente.

Pour le moment les données seront entrées à la main, je suis en stage pour une durée de 1 mois encore, je n'aurai pas le temps, vu mes connaissances, d'automatiser les incrémentations de données car elles sont dispersées dans pleins de fichiers à des emplacements différents, il faut faire du recherchev, parfois les données dans certains fichiers sont en mois, il faut les cumuler pour les mettre en trimestres, ...

Le fichier que j'ai déjà constitué compare le T1 2015 au T1 2016.

Plusieurs objectifs :

Tout d'abord, il faut savoir que lorsque monsieur ou madame x va faire un copier coller de mon fichier, supprimer toutes les données et rentrer les données de T2 2015 et T2 2016, il pourra utiliser le userform suivant :

- Bouton 1 : les couleurs se mettent en forme selon les seuils spécifiés

- Bouton 2 : les pastilles jaunes se mettent dès lors que sur les plages (spécifiés dans mon post précédent), il y a des valeurs négatives

- Bouton 3 : Permet de créer sur le même fichier 64 feuilles (Oui c'est énorme haha)
--> il s'agit de copier coller de filtres, 4 feuilles -> 16 groupes -> 64 feuilles (1 feuille par groupe)

- Bouton 4 : Permet d'exporter les 64 feuilles en 16 fichiers comportant les 4 feuilles : autrement dit : 1 fichier par groupe avec ses 4 feuilles

- Bouton 5 et dernier bouton : Dans les 16 fichiers, j'aimerais ajouter une feuille qui comporte 3 tableaux :
tableau 1 : synthétise la première feuille (je dois aller chercher des données et les placées dans le tableau selon certains seuils)
tableau 2 : synthétise la deuxième et la troisième feuille (idem)
tableau 3 : synthétise la quatrième feuille (idem)

Je suis rendu au dernier bouton, je ne sais pas du tout encore comme je vais m'y prendre, je réfléchis à une méthode..

Voili voilou pour mon travail de stage ^^

[jeanmidudu]

Je ne savais pas que les MFC étaient plus lourdes que du code VBA, mais après test, c'est plus que vrai.

Du coup, il faudrait que dans son code il évite de faire appel aux fonctions MFC, et qu'il programme la mise en forme par lui-même.

[mateoa44]

Re,

Un truc tout bête pour commencer, au lieu d'utiliser plein de IF End If, utilise des ElseIf(ça fait gagner du temps sur le nombre de vérification car si trouve alors ne teste pas les autres). En plus de cela, tu n'es pas obligé d'activer les feuilles pour faire les modifs (perte de temps considérable), teste seulement le nom. Ce qui donne un truc qui ressemble à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
For m = 1 To maxfeuil
With Sheets(m)
If .name = "Téléphonie" Then
    .Range("H:H,L:L,Q:Q").Select
ElseIf .name = "Réclamations" Then
    .Range("I:I,L:L,X:X").Select
ElseIf .name = "Post it" Then
    .Range("I:I,L:L,R:R,U:U,X:X").Select
ElseIf .name = "Relation client" Then
    .Range("I:I,L:L,O:O,T:T").Select
End If
 
'La suite de ton code

Ceci ne fonctionne pas Jean =P

[mateoa44]

J'ai modifié un peu, Patrick dira que c'est pas beau tout ça, mais bon, ça marche haha (du select partouuuuuut)
Pas d'erreur quand on appuie de nouveau sur le bouton

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
maxfeuil = Sheets.Count - 1
For m = 1 To maxfeuil
If Sheets(m).Name = "Telephonie" Then
    Sheets(m).Select
    Sheets(m).Range("H:H,L:L,Q:Q").Select
ElseIf Sheets(m).Name = "Reclamations" Then
    Sheets(m).Select
    Sheets(m).Range("I:I,L:L,X:X").Select
ElseIf Sheets(m).Name = "Post it" Then
    Sheets(m).Select
    Sheets(m).Range("I:I,L:L,R:R,U:U,X:X").Select
ElseIf Sheets(m).Name = "Relation client" Then
    Sheets(m).Select
    Sheets(m).Range("I:I,L:L,O:O,T:T").Select
End If

[jeanmidudu]

Peux-tu préciser ce qui ne fonctionne, stp?

Car comme je l'ai mentionné plus haut, il faut faire attention à plusieurs choses :
-Utiliser la bonne propriété name pour tes feuilles (je te conseille de donner le même nom aux 2 propriétés name)
-Eviter les caractères spéciaux (j'avais dit les accents, mais évite aussi les espaces, privilégie plutôt des underscore)

Et comme on te l'a indiqué avec Patrick, évite autant que possible d'utiliser SELECT

Il faut adapter le code en conséquence évidemment.

Si tu as une erreur, peux-tu me dire laquelle?

Enfin, qu'entends-tu pas "pastille jaune"? A quels endroits doivent-elles être mises?

Et petit détail mais très important :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

maxfeuil = Sheets.Count - 1 '-1 car en réalité j'ai 5 feuilles, la dernière contient le bouton qui ouvre le userform

FAIS TRES ATTENTION, si quelqu'un bouge tes feuilles, le reste de ton code risque de ne pas fonctionner. Un conseil, retire le -1 (et cela peut être intéressant en cas d'ajout dans le fichier)

Enfin, un petit truc réutilisant ton premier ButtonCommande10,
inutile de faire les traitements à chaque passage dans la boucle, je te conseille d'utiliser un booléen dans les If, qui passe à true quand une feuille utile est trouvé, et le traitement ne se faisant que dans ce cas là, sinon le booléen = false, donc pas de traitement
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Dim trouve As boolean
For ...
 
trouve = false
 
If Sheets("Relation client").Select Then
    Range("I:I,L:L,O:O,T:T").Select
    trouve = true
End If
 
     If trouve then
   '                CODE FOURNI AVEC ENREGISTREUR DE MACRO POUR MISE EN FORME CONDITIONNELLE uniquement sur les valeurs négatives (pastille jaune)  
        Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
     End If

[mateoa44]

J'ai modifié le nom des feuilles, j'ai enlevé les accents. L'erreur est la suivante : "Erreur d'exécution '1004' : La méthode Select de la classe Range a échoué."
Je ne connais pas la propriété name, je ne sais pas ce que veut dire "donner le même nom à la propriété name"
Je suis vraiment un novice comme je l'ai dis ^^

Les pastilles jaunes sont mise sur les plages de données spécifiées dans le code, dès lors que la valeur <0 (c'est la pastille jaune quand tu vas dans MFC, boutons)

Si j'enlève le -1 dans le code où je duplique les feuilles en 64 feuilles je risque d'avoir une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub CommandButton1_Click()
 
Dim i As Integer
Dim j As Integer
Dim maxfeuil As Integer, m As Integer
maxfeuil = Sheets.Count - 1
For m = 1 To maxfeuil
 Sheets(m).Activate
        For i = 1 To 9
            Range("B1").Activate
            Selection.AutoFilter
            ActiveSheet.Range("A1:AX500").AutoFilter Field:=2, Criteria1:="10" & i
            Range("A1:AX500").Select
 
            Selection.Copy
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Paste
            Sheets(m).Select
            Application.CutCopyMode = False
            Selection.AutoFilter
        Next
            For j = 10 To 16
                Range("B1").Activate
                Selection.AutoFilter
                ActiveSheet.Range("A1:AX500").AutoFilter Field:=2, Criteria1:="1" & j
                Range("A1:AX500").Select
 
                Selection.Copy
                Sheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Paste
                Sheets(m).Select
                Application.CutCopyMode = False
                Selection.AutoFilter
            Next
 Next
 
End Sub

[jeanmidudu]

Alors oui pour cette erreur tu as bien modifié le code En fait on ne peut pas faire sheets.range.select directement. Il faut d'abord faire sheets.select puis range.select (comme tu l'as fait).

Ensuite voici les propriétés name dont je parle :


Par contre, je ne vois pas à quoi correspond ton dernier code.

Pour revenir à l'utilisation des pastilles, peux-tu nous dire ce que fait FormatConditions(1)

[mateoa44]

Ok je vois pour les propriétés name, sur ta capture d'écran, il y a deux "name", dans mon cas, le premier est intitulé "Feuil1" et le deuxième "Telephonie"

Par contre, je ne vois pas à quoi correspond ton dernier code.

Remonte un peu dans le topic, j'ai écris un 'moyen' pavé sur le objectifs de mon travail, j'explique à quoi il sert, le code correspond au bouton 1 ^^

Pour l'histoire sur les pastilles, honnêtement, je n'en ai aucun idée...

Ceci fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub CommandButton10_Click()
Dim maxfeuil As Integer, m As Integer
maxfeuil = Sheets.Count - 1
For m = 1 To maxfeuil
With Sheets(m)
If .Name = "Telephonie" Then
    .Select
    .Range("H:H,L:L,Q:Q").Select
ElseIf .Name = "Reclamations" Then
    .Select
    .Range("I:I,L:L,O:O,R:R,U:U,X:X").Select
ElseIf .Name = "Post it" Then
    .Select
    .Range("I:I,L:L,R:R,O:O,U:U,X:X").Select
ElseIf .Name = "Relation client" Then
    .Select
    .Range("I:I,L:L,O:O,T:T").Select
End If
 
'Suite du code

[jeanmidudu]

D'accord.

Pour les pastilles, je pense que tu peux encore améliorer le code pour qu'il ne soit pas trop long, en gérant toi-même la mise en forme, donc sans faire appel à FormatConditions(1) mais il faut que tu saches ce que cette fonction est censée faire.