[VBA] Plage de cellule variable puis formule & mise en forme

[ludovic76]

Bonjour à toutes et à tous,

Travaillant actuellement sur des tableau contenant plus de 60 000 lignes, je cherche à automatiser certains processus. J'ai appris il y a peu l'existence de Visual Basic et de son efficacité impressionnante.

Je vous explique mon idée. J'ai une liste de codes objets avec des poids différents et des ventes associées.

X1 20kg vendu 2 fois
X1 22kg vendu 5 fois
X2 13kg vendu 12 fois
X2 14kg vendu 2 fois
X2 22kg vendu 6 fois
X3 9kg vendu 52 fois etc ...

Ce que je cherche à faire, c'est de pouvoir dans un 1er temps, distinguer chaque article dans une plage distincte (qui sont de tailles variables).

Et ensuite au sein de chaque plage, je veux appliquer une mise en forme où le poids maximum pour l'article parmi tous les poids proposés, devient en police de couleur rouge par exemple.

Puis la même mise en forme pour l'occurrence des ventes. Mais je suppose qu'une fois qu'on applique une mise en forme sur une plage, le code est similaire.

J'espère avoir été assez clair, je joins à cette demande un fichier excel qui illustre mes propos.

Merci d'avance,

Ludo

 

[Modeste]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour Ludovic,

Une partie du travail pourrait vraisemblablement être faite avec des Mises en Forme Conditionnelles (mais pour une autre partie, je ne voyais pas trop comment y arriver ... en tous cas pas sans accoucher d'un monstre)

Une proposition donc par macro. Deux boutons dans la feuille: l'un permet de colorer, l'autre de supprimer toutes les couleurs (sauf les titres). Pour autant que j'aie pu voir, ça m'avait l'air de donner les mêmes résultats que ce que tu avais fait.

Teste d'abord dans le fichier joint et quand tu seras certain qu'il convient, tu étendras les tests à des plages plus "vastes".
Créé en Excel 2010, le fichier devrait tourner sous 2003 (les couleurs pourraient varier, le cas échéant)

Edit: quand je parlais de créer un monstre ... v'là job qui se pointe

 

[job75]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour ludovic76,

Voici des solutions uniquement par Mise en forme conditionnelle (MFC) :

1) pour Excel 2007/2010 (fichier .xlsx) avec 4 conditions pour la MFC

2) pour Excel 2003 (fichier .xls) avec 3 conditions.

Sur Excel 2003 la 4ème condition avec police "Gras" n'est pas possible (3 conditions maximum).

Nota : le tableau doit bien sûr être trié sur la colonne A.

Edit : ah mais Modeste viens juste de se pointer, salut cher Modeste

A+

 

[Robert]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour le fil, bonjour le forum,

Une proposition VBA avec le code ci-dessous :

Sub Macro1()
Dim dl As Long 'déclare la variable dl (Dernière Ligne)
Dim pli As Long 'déclare la varialbe pli (Première LIgne de la plage du produit)
Dim dli As Long 'déclare la varialbe pli (Dernière LIgne de la plage du produit)
Dim plp As Range 'déclare la varialbe plp (PLage du Produit)
Dim i As Long 'déclare la varialbe i (incrément)
Dim limaxc As Long 'déclare la varialbe limaxc (LIgne de la valeur MAX en colonne C)
Dim limaxd As Long 'déclare la varialbe limaxd (LIgne de la valeur MAX en colonne D)

Application.ScreenUpdating = False 'masque les changements à l'écran
With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
    'tri les valeur par ordre croissant par rapport à la colonne A
    .Range("A1").CurrentRegion.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    dl = .Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne dl du tableau
    .Range("A2:E" & dl).ClearFormats 'supprime les formats sur l'ensemble des données (sans la première ligne)
    For i = 2 To dl 'boucle de 2 à dl
        pli = i 'définit la première ligne de la plage du produit pli
        'définit la derniè ligne de la plage du produit pli
        dli = Application.WorksheetFunction.CountIf(.Range(.Cells(pli, 1), Cells(dl, 1)), Cells(pli, 1).Value) + (pli - 1)
        Set plp = .Range(.Cells(pli, 1), Cells(dli, 1)) 'définit la plage du produit en colonne A
        'récupère la ligne de la valeur max en colonne C limaxc
        limaxc = plp.Offset(0, 2).Find(Application.WorksheetFunction.Max(plp.Offset(0, 2)), , xlValues, xlWhole).Row
        'récupère la ligne de la valeur max en colonne D limaxd
        limaxd = plp.Offset(0, 3).Find(Application.WorksheetFunction.Max(plp.Offset(0, 3)), , xlValues, xlWhole).Row
        .Range(.Cells(limaxd, 1), .Cells(limaxd, 5)).Font.ColorIndex = 5 'couleur bleu sur la ligne de la valeur max en colonne D
        .Cells(limaxd, 4).Font.Bold = True 'gras sur la cellule de la valeur max en colonne D
        .Range(.Cells(limaxc, 1), .Cells(limaxc, 5)).Font.ColorIndex = 7 'couleur bleu sur la ligne de la valeur max en colonne C
        .Cells(limaxc, 3).Font.Bold = True 'gras sur la cellule de la valeur max en colonne D
        'colore la plage jusqu'à la colonne E en fonction de la couleur au dessus
        plp.Resize(plp.Rows.Count, 5).Interior.ColorIndex = IIf(plp.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = 47, xlNone, 47)
        i = dli
    Next i
End With
Application.ScreenUpdating = True 'affiche les changements à l'écran
End Sub

Le fichier :

[Édition]
Je navet pas pris le temps de regarder la proposition de Modeste avant de poster. C'est assez similaire mais je préfère nettement sa methode à lui...

 

[job75]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Re,

Pour le fun une solution qui fonctionne sur toute version Excel.

La 4ème condition - "Gras" - est réalisée par cette macro :

Sub Gras()
'se lance par Ctrl+A
Dim plage As Range, cel As Range, F As String
Application.ScreenUpdating = False
On Error Resume Next
Set plage = [C:D].SpecialCells(xlCellTypeConstants, 1)
plage.Font.Bold = False
For Each cel In plage
  cel.Select
  F = "AND(COLUMN()=3,ROW()=MATCH(MAX(OFFSET(PlageCode,,2)),OFFSET(PlageCode,,2),0)+MATCH($A" & cel.Row & ",$A:$A,0)-1)+AND(COLUMN()=4,ROW()=MATCH(MAX(OFFSET(PlageCode,,3)),OFFSET(PlageCode,,3),0)+MATCH($A" & cel.Row & ",$A:$A,0)-1)"
  If IsNumeric(Evaluate(F)) Then If Evaluate(F) Then cel.Font.Bold = True
Next
[A1].Select
End Sub

Noter que les polices bleue ou rouge ne sont plus en Italique.

Noter aussi - horreur et putréfaction - que la boucle sélectionne la cellule (pour obtenir PlageCode).

Edit : salut Robert, pas rafraîchi.

A+

 

[job75]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour le fil, le forum,

Avec 60000 lignes, la condition MFC utilisant SOMMEPROD (couleur de fond) prendra un temps fou.

C'est donc cette condition qu'il faut remplacer par une macro :

Sub CouleurFond()
'se lance par Ctrl+A
Dim derlig&
derlig = Cells(Rows.Count, 1).End(xlUp).Row
If derlig = 1 Then Exit Sub
Application.ScreenUpdating = False
Range("A2:E" & Rows.Count).Interior.ColorIndex = xlNone 'RAZ
[A:A].Insert 'insertion d'une colonne auxiliaire
Range("A2:A" & derlig).FormulaR1C1 = "=R[-1]C+(RC2<>R[-1]C2)"
[G2] = "=MOD(A2,2)" 'critère du filtre avancé
Range("A1:F" & derlig).AdvancedFilter xlFilterInPlace, [G1:G2]
Range("B2:F" & derlig).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 15
Range("A1:F" & derlig).AdvancedFilter xlFilterInPlace, ""
[G2] = ""
[A:A].Delete
End Sub

La macro utilise une colonne auxiliaire et le filtre avancé (élaboré).

Fichier (2).

Sur 60000 lignes il sera intéressant de comparer les durées d'exécution de cette version et de celle de Modeste.

A+

 

[Modeste]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour tout le monde,

Sur 60000 lignes il sera intéressant de comparer les durées d'exécution de cette version et de celle de Modeste

mais pourquoi tant de haine!?

 

[job75]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Re Modeste,

En fait comme chacun sait je hais VBA

J'ai tenté un test sur mon dernier fichier avec 10000 valeurs en colonne A.

MFC et macro prennent déjà pas mal de temps.

Je crains qu'avec 60000 ça devienne assommant.

Edit : J'ai testé ta solution Modeste, c'est sans conteste plus rapide.

A+

 

[Modeste]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour le fil

En fait comme chacun sait je hais VBA

Quoi, toi aussi, comme tant d'autres!? A qui se fier, je vous le demande?

J'ai testé ta solution Modeste, c'est sans conteste plus rapide

Ben oui, moi je le savais quand tu ne sais pas job, demande-moi Je ne sais pas pourquoi, mais j'ai le sentiment que d'ici peu, tu vas encore nous sortir un lapin improbable d'un chapeau


PS: ... mais où donc est passé Ludovic76

 

[ludovic76]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour à tous,

Merci tout d'abord pour l'intérêt que vous avez porté à mon problème. Je testerai cette solution demain car je n'ai pas le fichier sur ce pc.

Modeste, tu as eu raison d'employer le terme "monstre", j'ai pris peur en voyant l'écriture sous VBA ...

a+

 

[ludovic76]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour à tous,

Vu que vous avez pris du temps pour réaliser ces écritures, j'ai pris du temps pour tester chacune de vos solutions.

@Modeste, ton fichier fonctionnement parfaitement bien ! Le code est tellement optimisé que je ne pourrais pas le reproduire par contre ^^'

@ Job75, la mise en couleur des formes fonctionne bien, cependant le code vba pour la mise en gras ne fonctionne pas chez moi. Sinon le faire en mode MFC est une bonne idée, même si je pense que c'est plus lourd à appliquer, mais probablement moins long à écrire, encore que...

@Robert, code vba fonctionne parfaitement bien, et j'ai apprécié les notes sur chaque ligne ! Je comprends un peu plus facilement l'écriture du code. Même si ça reste encore compliqué.


Merci à tous pour vos solutions, c'est très sympathique de votre part. Pour info, combien de temps cela vous à t-il pris à peu près ?

Ludo

 

[job75]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Bonjour Ludovic, le fil,

@ Job75, la mise en couleur des formes fonctionne bien, cependant le code vba pour la mise en gras ne fonctionne pas chez moi.

Etant sur Excel 2010, je n'avais pas pu tester pour Excel 2003.

Je pense que la 3ème condition "Gras" doit être réalisée par VBA.

De toute façon mes posts montrent qu'une MFC n'est pas la bonne solution, autant tout faire par VBA.

Quant au temps passé, j'ai dû bricoler 1h30 au total.

A+

 

[ludovic76]

Re : [VBA] Plage de cellule variable puis formule & mise en forme

Oui c'est vrai que VBA fonctionne mieux sur ce genre de tâche avec mises en forme multiples. Avec de la maîtrise de VBA, le gain de temps est énorme, mais pour le maîtriser, le chemin est long !

Je galère tellement sur chaque problème que j'ai, que je le fais manuellement ... il va falloir que je me penche sur les tutos

Merci encore

A+