XL 2016 MFC en VBA avec offset

[flyingmuad]

Bonjour à tous,

Encore un problème avec ma macro de devis
Mon devis peut comporter plusieurs chapitre avec chacun plusieurs ligne d'article
Chaque article appartenant à une famille un est affecté d'un coef de vente
colonne_prix de vente = colonne prix_untaire * colonne_quantité * coef_de_vente
colonne prix_untaire et colonne_quantité sont saisies
Coef_de_vente =coef_famille * coef_commercial
Mon coef_famille est issu d'une liste
Par faciliter de lecture j'affecte une couleur à chaque valeur de coef famille :
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & ji
Selection est ma plage dans le chapitre de ma colonne coef_famille
ji est issu d'une boucle :
For ii = 1 To 7
For jj = 1 To 8
ij = ij + 1: ji = ji + 0.01
ET me permet de colorer ma cellule différemment suivant sa valeur
Selection.FormatConditions(ij).Interior.ColorIndex = 8 * (ii - 1) + jj
Cela me donne 56 conditions différentes par cellule

Tous cela fonctionne parfaitement

Mon souci survient quand coef_commercial est différent de 1
La valeur de la cellule devient hors limite de ma MFC de base et je n'ai plus de mise en forme relative au coef_famille

J'ai donc dans la macro recopier la valeur de coef_famille dans une colonne, et je souhaite effectuer la MFC depuis cette colonne et non plus la colonne coef_de_vente
J'ai modifié ma ligne comme suit :
xa = "=$ah" & ActiveCell.Row & "=" & ji par exemple xa = "=$AH12=1.25"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=xa

Mais comme expliqué plus haut je traite plusieurs ligne à la fois ( d'ou le "selection.") et ma formule ne reflète pas le numero de ligne et reste constante avec le numero de la première ligne de la sélection

Je pense essayer de modifier ma macro par un appel à cette routine ç chaque ligne plutôt que par chapitre. Mais cela me fait faire de grosses modif
J'avoue ne pas maitriser la MFC sous VBA d'où mon appel à vos connaissances avant de me lancer la dedans car il y a peut être un moyen plus simple.

Merci de votre aide

Patrice

 

[TooFatBoy]

Bonjour,

Je n'arrive pas à ouvrir ton fichier exemple anonymisé.
Pourtant j'essaye...

 

[flyingmuad]

Bonjour,

Effectivement y'a un souci, désolé.

Comme cela ce doit être mieux

Ci joint donc, fichier généré à partir d'un classeur vierge suivant les choix de l'utilisateur.
Il est dans l'état ou la macro de mise en place des MFC de la colonne <L> va se lancer

Le champs sélectionné est L10:L18

La couleur à l'interieur de chaque celulle est définie par la famille de celui ci (definie par l'utilisateur, mais standard par défaut soit 1.12)
Pour definir le prix de vente final, une procédure "valeur cible" modifie la valeur de <BA5>
Pendant la création du devis BA5=1 la distinction est bien faite, voir ci dessous

Mais dès que on génère le prix de vente la MFC disparait (c'est normal), mais je ne le voudrais pas

Je reporte donc lors du choix du coef initial sa valeur plus loin dans le tableau (colonne AH)
Et c'est à partir de cette valeur (qui ne changera pas à la vente) que je veux faire ma MFC

Dans la macro jointe (c'est une routine) il y a les lignes de base qui fonctionnent) et la version en developpement.
NB : Quand on la lance, en l'état, EXCEL se ferme !


Le fichier ici est en .xlsm pour y intégrer la macro qui fonctionne. En réel c'est un .xlsx et la macro est dans XLSTART

Merci de vos lumières

Bien cordialement

PAtrice

 

[flyingmuad]

Zut le fichier !

 

[TooFatBoy]

Comme cela ce doit être mieux

Non, mais le troisième essai est le bon.

Si personne ne t'a répondu d'ici-là, je regarde cette nuit.

 

[flyingmuad]

Super, merci
Mais faut dormir aussi

 

[TooFatBoy]

J'ai regardé le fichier et la macro m'a l'air de fonctionner.

 

[flyingmuad]

Bonjour,

Oui elle fonctionne avec comme référence pour la MFC la cellule elle meme. Mais lorsque je la modifie pour bypasser la première MFC et utiliser celle avec une référence relative, ca ne marche plus!

 

[flyingmuad]

Pour apporter plus de précisions :

Comme je l'ai écris au début le fichier est dans l'état quand la macro plante.
C'est à dire que la mise en forme générale du fichier n'est pas terminée.
La plage AT1:BJ6 me sert à stocker les paramètres de configuration du fichier (un peu comme un .ini)

Bref en clair ce que je voudrais c'est faire fonctionner la ligne :

Selection.FormatConditions.Add Type:=xlExpression, Formula1:=xa

avec
xa = "=$ah" & ActiveCell.Row & "=" & ji
ou
xa = "=$ah" & c.Row & "=" & ji

c'est cette ligne qui plante lorsque je supprime la remarque de la ligne:
' GoTo test_mef_coef
pour zapper la ligne de MFC qui fonctionne.

J'espère avoir été plus clair

Bien cordialement

Patrice

 

[TooFatBoy]

Moi je dirai que, en vraiment clair, tu veux simplement colorier la colonne L en fonction de la valeur de la colonne AH. Nan ?!?
T'as vu, c'est dit en une seule petite phrase toute simple et c'est clair.

Si c'est bien ça, alors essaye ceci :

Sub mfc_coef()
'
Dim plage As Range, plage_cond1 As Range, plage_cond2 As Range
Dim ji, ii, jj, ij
Dim MaPlage As Range

    Set MaPlage = Sheets("test001").Range("L10:L" & Range("L" & Rows.Count).End(xlUp).Row)

    With MaPlage

        .NumberFormat = "0.00"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .FormatConditions.Delete

        ji = 0.89

        For ii = 1 To 7

            For jj = 1 To 8

                ij = ij + 1
                ji = ji + 0.01

                .FormatConditions.Add Type:=xlExpression, Formula1:="=$AH10=" & ji
                .FormatConditions(ij).Interior.ColorIndex = 8 * (ii - 1) + jj
                .FormatConditions(ij).StopIfTrue = False

                If ij = 2 Or ij = 6 Or ij = 19 Or ij = 20 Or ij = 27 Or ij = 28 Or ij = 34 Or ij = 35 Or ij = 36 Then
                    .FormatConditions(ij).Font.Color = -1003520
                Else
                    .FormatConditions(ij).Font.Color = -16711681
                End If

            Next jj

        Next ii

    End With

End Sub

 

[flyingmuad]

Bonjour,

Impeccable, merci
J'ai modifié la macro pour couvrir tous mes besoins (non exprimés encore!) pour info si ca t interresse:
Le coef n'est pas toujours dans la colonne L (fichier issu de client avec un nombre de colonne de base variable)
La colonne AH peut varier aussi mais est toujours avec un offset de 22 / colonne(L)
Chaque chapitre est suivi d'un sous total avec une mise en forme particulière. Je ne peux donc pas mettre de MFC sur les lignes entre les chapitre

Des lignes et/ou chapitres peuvent être ajouter après coup. La macro ne couvrira que les lignes insérées.

Enfin je traite la MFC ligne par ligne car dans la tienne les MFC se font toutes en fonction de la ligne AH10.voir ci dessous la MFC de la ligne12

Ta macro fonctionne, mais je ne comprends pas pourquoi et ca m'ennuie de mettre à disposition des fonctionnalité que je ne comprends pas
Or, c'est en fonction de la valeur de chaque ligne d'ou la boucle sur xc pour couvrir toute la sélection et la modif dans la formule avec Cells(xc,xe) et cela donne :

Visuellement (et c'est le but) le résultat est le même mais j'ai préféré cette modification (j'avoue ne pas maîtriser ces MFC... donc).

En tous cas merci pour ton aide
Bien cordialement

Patrice

Sub mfc_coef4()

'declarations 
Dim plage As Range
Dim plage_cond1 As Range
Dim plage_cond2 As Range
Dim xa, xb, xc, xd, xe
Dim ji, ii, jj, ij

xa = ActiveCell.Row
xb = selection.Rows.Count - 1
xd = ActiveCell.Column
xe = xd + 22

For xc = xa To xa + xb
ji = 0: ij = 0
    
    Cells(xc, xd).NumberFormat = "0.00"
    Cells(xc, xd).HorizontalAlignment = xlCenter
    Cells(xc, xd).VerticalAlignment = xlCenter
    Cells(xc, xd).FormatConditions.Delete
        
    ji = 0.89
    
    For ii = 1 To 7
        For jj = 1 To 8
        
        ij = ij + 1: ji = ji + 0.01

        Cells(xc, xd).FormatConditions.Add Type:=xlExpression, Formula1:="=" & Cells(xc, xe).Address & "=" & ji
        Cells(xc, xd).FormatConditions(ij).Interior.ColorIndex = 8 * (ii - 1) + jj
        Cells(xc, xd).FormatConditions(ij).StopIfTrue = False
        
        If ij = 2 Or ij = 6 Or ij = 19 Or ij = 20 Or ij = 27 Or ij = 28 Or ij = 34 Or ij = 35 Or ij = 36 Then
            Cells(xc, xd).FormatConditions(ij).Font.Color = -1003520
        Else
            Cells(xc, xd).FormatConditions(ij).Font.Color = -16711681
        End If
    
        Next jj
    Next ii
 Next xc

End Sub

 

[TooFatBoy]

Ta macro fonctionne, mais je ne comprends pas pourquoi et ca m'ennuie de mettre à disposition des fonctionnalité que je ne comprends pas

As-tu besoin de renseignements concernant la macro ou la MFC ?

Mais si tu as réussi à la modifier, c'est que tu as compris ce qu'elle fait, non ?


ps : dans la macro j'ai mis $AH10, mais c'est vrai que le $ est inutile.

 

[flyingmuad]

Non, la macro c'st ok,

Je l'ai mise en ligne après l'avoir testée, pas de souci.

Pour les MFC, c'est vrai que j'ai du mal, mais ca roule. le délicat c'est la bonne syntaxe des formues.

Merci de ton aide.

 

[TooFatBoy]

Ta macro fonctionne, mais je ne comprends pas pourquoi et ca m'ennuie de mettre à disposition des fonctionnalité que je ne comprends pas
Or, c'est en fonction de la valeur de chaque ligne d'ou la boucle sur xc pour couvrir toute la sélection et la modif dans la formule avec Cells(xc,xe)

Je crois que je vois ce que tu ne comprends pas.

En fait, la MFC s'adapte automatiquement.
Si dans la formule tu fais référence par exemple à la cellule AH10, et que tu appliques la MFC par exemple aux lignes 10 à 15, La MFC fera automatiquement référence à la cellule AH10 pour la ligne 10, référence à la cellule AH11 pour la ligne 11, etc. jusqu'à la ligne 15.

C'est ça qui te chagrinait ???

 

[flyingmuad]

Je pinaille surtout avec des formules dans la condition. Je maitrise à peu pres les ref absolues et relatives yc en VBA mais dans les MFC , ca couine.