Grand Défi VBA - Comparaison et formattage conditionnel assez délicat
Bonjour tout le monde,
je suis tout nouveau dans le monde de vba..j'ai un tableau d'une centaines de lignes et colonnes où je dois faire un formattage conditionnel.
la première colonne dresse les années (2015-2016-2017), la deuxième colonne (les clients) et les autres colonnes, les montants des contrats.
on me demande de chercher si la valeur de contrat a diminué d'une anné à l'autre et si c'est le cas colorer la cellule correspondant à la valeur qui contient le montant le plus bas.
il s'agit pas de chercher la valeur la plus bas pour tout une colonne mais pour chaque client pour trois années (A chaque 3 lignes de la colonne donc pour chaque client, il faut comparer la valeur de 2015 avec 2016 et 2017, ressortir la valeur la plus bas puis colorer cette valeur uniquement si ca tombe dans l'anné la plus récente (selon la comparaison) ( si par exemple en 2017 on n'a pas de valeur, on compare 2015 et 2016 puis si la valeur en 2016 est inférieur à celle en 2015 on colore la valeur en 2016, sinon on fait rien
pour comprendre de quoi il s'agit, J'ai joint un exemple en Excel avec deux onglets, un onglet de données exemple et un onglet du résultat souhaité...
je chercher donc un macro qui:
- pour chaque client et chaque contrat, il compare les valeurs de contrat des trois années.
- pour chaque client et chaque contrat, si la valeur de contrat d'une année est vide, il l'ignore et compare les valeurs entre les deux autres.
- pour chaque client et chaque contrat, il met en couleur la valeur la plus faible (de la comparaison) UNIQUEMENT lorsque cette valeur se retrouve dans l’année la plus récente.
Pour ceux qui viennent de voir ce post, merci de lire mes réponses pour ne pas gaspiller vos efforts
Bonjour kasper2004, bienvenue sur le forum,
à tester,
Sub test()
n = Cells(Rows.Count, 1).End(xlUp).Row
For col = 3 To 7
For i = 2 To n Step 3
For k = i + 2 To i + 1 Step -1
If Cells(k, col) <> 0 Then
m = Application.Min(Range(Cells(i, col).Address, Cells(k, col).Address))
If Cells(k, col) = m Then Cells(k, col).Interior.Color = 49407: Exit For
End If
Next
Next
Next
End SubBonjour,
Bravo Isabelle qui a relevé le grand défi VBA !
L'une des premières règles du VBA est aussi de savoir ... s'en passer. Voici une solution sans VBA !
sabV...Merci bcp pour le code..je ne sais pas il y a qq chose qui marche pas avec la 3ème ligne (celle de l'anné 2017). il y a d'autres commentaires que j'ai mis sur le fichier ci-joint. Dans ce fichier j'ai rempli les cellules du tableau avec tous les cas possible pour que tu ailles une idée..mais vraiment tu es très proche de la solution...Bravo!!!!...après avoir réussi à écrire le bon code, J'aimerais bien que tu me montreras comment passer de 4 colonnes à des milliers...même chose pour les lignes...Merci Infiniment pour ton aide!
Steelson, Merci pour votre message...mon fichier de travail comme j'ai mentionné comporte des centaines de colonne et des miliers de lignes et je pense pas que ta method marcheras pour un fichier volumineux. l'exemple joint au premier message c juste un tout tout petit tableau pour tester le macro.
Merci infiniment à tous ceux et celles qui vont participer à la resolution de ce cas un peu complexe pour moi.
Bonjour,
Une piste un peu similaire à celle de sabV avec une boucle en moins mais une batterie de tests :
Sub Test()
Dim C As Range
Dim Col As Long
Dim Lig As Long
Dim DerLig As Long
Dim I As Integer
DerLig = Cells(Rows.Count, 1).End(xlUp).Row
For Col = 4 To 8: For Lig = DerLig To 2 Step -3
Set C = Cells(Lig, Col) 'pour raccourcir un peu les lignes !
If C.Value <> "" Then
If C.Offset(-1).Value <> "" Then
If C.Value < C.Offset(-1).Value Then C.Interior.Color = 49407
Else
If C.Value < C.Offset(-2).Value Then C.Interior.Color = 49407
End If
Else
If C.Offset(-1).Value <> "" Then If C.Offset(-1).Value < C.Offset(-2).Value Then C.Offset(-1).Interior.Color = 49407
End If
Next Lig, Col
End SubNon c'est un a priori erroné. mais <isabelle a parfaitement répondu ... en VBA.Steelson, Merci pour votre message...mon fichier de travail comme j'ai mentionné comporte des centaines de colonne et des miliers de lignes et je pense pas que ta method marcheras pour un fichier volumineux. l'exemple joint au premier message c juste un tout tout petit tableau pour tester le macro.
Bonjour Theze,
Merci pour votre effort.malheureusement votre code ne donne pas le résultat souhaité. peut être que j'ai mal expliqué ma demande
En fait le macro doit travailler en deux étapes: comparaison et mise en forme.
le macro doit comparer les trois cellules corespondant aux années 2015,2016 et 2017 pour chaque client et chaque contrat.Par exemple (pour le client 2 et le contrat 3, il doit comparer les cellules F5,F6 et F7. Dans ce cas, il y a trois possibilités:
1-1- si l'une des trois cellules est vide , il va l'ignorer et comparer les deux autres. Dans ce cas il y a deux possibilities:
1-1-a si la valeur la plus petite de la comparaison est celle de l'année la plus récente, il met cette valeur en couleur.
1-1-b sinon, il ne met pas de couleur
1-2- si il y a juste une seule cellule parmi les trois qui contient une valeur, il fait rien.
1-3 si les trois cellules contiennet des valeurs, il va ignorer l'année 2015 et comparer les valeurs de 2016 et 2017. deux cas qui se présentent:
1-3-a si la valeur la plus petite de la comparaison est celle de l'année la plus récente, il met cette valeur en couleur.
1-3-b sinon, il ne met pas de couleur.
le macro doit alors contenir des conditions imbriquées et des boucles pour parcourir les colones par un step de 1 à partir de la colonne D et les lignes par un step de 3 à partir de la ligne 2 comme il a fait SabV.
je rejoint le fichier. il y a deux onglets: Onglet Données et Onglet résultat souhaité. Dans ce fichier, J'ai mis tous les possibilities de comparaison et le résultats que le Macro doit normalement faire sortir.
A vos clavier!
Merci à tous le monde
Kasper ... c'est ce qui s'appelle faire compliqué quand on peut faire simple via la MFC (qui plus est n'a pas besoin d'être activée car elle tient compte des modifications naturellement, comme tout format du reste).
Mais c'est toi le "client", c'est toi qui choisis.
Salut Kasper,
salut l'équipe,
tiens donc, la structure du fichier a déjà changé depuis le premier post!
Heureusement que j'ai attendu un peu!
Pas le temps de peaufiner pour l'instant : premier jet!
Un double-clic en 'Données' démarre la macro.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tData
Cancel = True
Application.ScreenUpdating = False
'
tData = Range("A1").Resize(UsedRange.Rows.Count, UsedRange.Columns.Count).Value
For x = 2 To UBound(tData, 1) - 2 Step 3
For y = 4 To UBound(tData, 2)
iIdx = 0
If tData(x + 2, y) <> "" Then
If tData(x + 1, y) <> "" Then
iIdx = IIf(tData(x + 2, y) < tData(x + 1, y), 2, 0)
Else
iIdx = IIf(tData(x + 2, y) < tData(x, y), 2, 0)
End If
Else
If tData(x + 1, y) <> "" Then iIdx = IIf(tData(x + 1, y) < tData(x, y), 1, 0)
End If
Cells(x + iIdx, y).Interior.Color = IIf(iIdx > 0, RGB(215, 215, 215), xlNone)
Next
Next
'
Application.ScreenUpdating = True
'
End SubA+
Je suis tenace je sais, ne serait-ce que pour soigner mes neurones....
j'ai refait la MFC pour tenir compte de toutes tes règles
salut curulis57,
Ton macro marche super bien! Merci!!
j'ai trois questions
1- est ce possible d'associer un bouton au code au lieu de faire un double clique..comme ça si une valeur change, on refait la mise en forme?
2- si jamais au lieu d'avoir 3 ans de données j'ai par exemple 5 ou 10, je pense que je dois changer step dans le code..c ça?
3- y a-t-il une possibilté d'améliorer le code de telle sorte qu'à l'exécution de macro, il me sort une fenêtre de dialogue pour que je choisisse mes x et y au lieu de les fixer à 2 et 4 respectivement ou de les changer dans le code à chaque fois. genre demander à l'utilisateur des i, j et changer le code :
For x = i To UBound(tData, 1) - 2 Step 3
For y = j To UBound(tData, 2)
voilà...Merci à tous les personnes qui ont participé. vous êtes tous géniaux...bravo! ..grâce à vous tous j'ai adoré ce forum et attendez d'autres défis
Merci Steelson,
tu es un génie de MFC
moi, j'ai trouvé la lampe d'Aladdin, mais en la frottant, j'ai pas eu d'chance : y'a aucun génie MFC qui est apparu !
c'est sûrement pour ça qu'toutes mes MFC foirent lamentablement !
Merci Steelson,
tu es un génie de MFC
merci pour ton humour ... sarcastique, mais tu y viendras ...
moi, j'ai trouvé la lampe d'Aladdin, mais en la frottant, j'ai pas eu d'chance : y'a aucun génie MFC qui est apparu !
c'est sûrement pour ça qu'toutes mes MFC foirent lamentablement !
ah mais mon cher Dhany, tu dis cela mais je suis sûr de ton humilité dans ce domaine
bonjour à tous
vous me connaissez, je préfère, et de très loin, la solution de Steelson par MFC
les MFC sont plus simples, et pas moins rapides, même sur des milliers de données.
et une fois que tu as compris, pas besoin de faire appel à des spécialistes pour en refaire (contrairement à VBA)
note : d'une manière générale, c'est une mauvaise idée de créer par VBA ce qui existe déjà.
amitiés à tous
Bonjour,
les MFC sont plus simples,
d'accord
et pas moins rapides, même sur des milliers de données.
ça, ça reste à voir...
On ne compte plus sur les forums les fichiers devenus impossibles à utiliser suite aux lenteurs à cause de trop nombreuses MFC, et qui retrouvent toute la réactivité dès leur suppression.
Je me demande d'ailleurs si elles ne sont pas volatiles vu le fort impact qu'elles ont.
eric
Salut tout le monde,
je te remercie, jmd, au nom de tous ceux qui préfèrent (aiment) VBA, de nous considérer comme des spécialistes!
Des amateurs éclairés, au mieux, dirais-je!
Voici une macro (toujours activable par double-clic) qui traitera un nombre d'années indéterminé et variable dans une même BDD automatiquement sans demander aucune info à l'utilisateur.
Condition pour que cette version ci fonctionne : que le nom des clients figure sur chaque ligne comme illustré dans le fichier-exemple.
Là, jmd et Steelson, avec tout mon respect, j'attends la MFC qui en fera autant (même si je l'espère, histoire d'en apprendre un peu plus...)!
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tData, iStart%
Cancel = True
Application.ScreenUpdating = False
'
Range("D2").Resize(UsedRange.Rows.Count, UsedRange.Columns.Count).Interior.Color = xlNone
tData = Range("A1").Resize(UsedRange.Rows.Count + 1, UsedRange.Columns.Count).Value
iStart = 2
For x = 3 To UBound(tData, 1)
If tData(x, 2) <> tData(x - 1, 2) Then
iStop = x - 1
For y = 4 To UBound(tData, 2)
For Z = iStop To iStart + 1 Step -1
iOK = 0
If tData(Z, y) <> "" Then
iOK = 1
For w = Z - 1 To iStart Step -1
If tData(w, y) <> "" Then
If tData(w, y) > tData(Z, y) Then
Cells(Z, y).Interior.Color = RGB(215, 215, 215)
iOK = 1
End If
Exit For
End If
Next
End If
If iOK = 1 Then Exit For
Next
Next
iStart = x
End If
Next
'
Application.ScreenUpdating = True
'
End SubKasper, avant de m'attaquer à une macro sensible au changement, de quel(s) changement(s) parles-tu :
- un changement de montant ?
- une insertion/suppression de ligne (année) ?
A+
Là, jmd et Steelson, avec tout mon respect, j'attends la MFC qui en fera autant (même si je l'espère, histoire d'en apprendre un peu plus...)!
je veux bien essayer, avec mon petit niveau en MFC (je galère parfois)
mais il faut me dire ce que tu veux
note : je fais des MFC utiles mais je refuse tout net de bricoler des tonnes de MFC qui prétendent transformer Excel en sapin de Noël inutilement. Comme on l'a vu dans des fils précédents.
jmd a écrit :note : je fais des MFC utiles mais je refuse tout net de bricoler des tonnes de MFC qui prétendent transformer Excel en sapin de Noël inutilement. Comme on l'a vu dans des fils précédents.
ça, faut avouer qu'y'en a qui lésinent pas sur les MFC !
et c'est un gros euphémisme vu qu'parfois, c'est à tire-larigot !
en veux-tu ? en voilà ! oui, jmd a raison : un vrai sapin d'Noël !
dhany
curulis57 a écrit :
Kasper, avant de m'attaquer à une macro sensible au changement, de quel(s) changement(s) parles-tu :
- un changement de montant ?
- une insertion/suppression de ligne (année) ?
ca se peut arriver les deux Mr Excel VBA
t'es vraiment un génie
1- est ce possible d'associer un bouton au code au lieu de faire un double clique..comme ça si une valeur change, on refait la mise en forme?
2- y a-t-il une possibilté d'améliorer le code de telle sorte qu'à l'exécution de macro, il me sort une fenêtre de dialogue pour que je choisisse mes x et y au lieu de les fixer à 2 et 4 respectivement ou de les changer dans le code à chaque fois. genre demander à l'utilisateur des i, j et changer le code :
For x = i To UBound(tData, 1) - 2 Step 3
For y = j To UBound(tData, 2)
ou encore mieux demander à l'utilisateur la cellule de départ qui est dans mon cas D2 (x=2 et y=4), car il se peut qu'on insère du texte avant le tableau et dans ce cas les repère change et ca pourrait affecter les résultats après exécution du macro.
3-est ce possible d'avoir une deuxième version du macro où on peut changer les valeurs 0 dans les cellules à comparer par des cellules vides car j'ai l'impression que j'aurai trop de couleur avec l'année 2017 du fait que j'ai pas saisi les montants que j'ai pas encore reçu et puisque j'ai mis un format personnalisé, Excel remplace les cellules vides par des € - .
Merciiiiiiiiiiiiiiiiiiiiii à tout le monde.. ce premier post m'encouragera à poster d'autres sujets. je suis sûr et certain que je me suis entouré des personnes géniales.