MFC via macro, là je cale

Le 09/01/2019 à 15:48

Bonjour à tous,

j'ai crée une macro via l'enregistreur de macro pour faire (entre autre) une mise en forme conditionnelle sur le fichier sample.csv joint.

Cette macro fait la chose suivante :

1) Elle "freeze les panes" en C2 -> OK !

2) (C'est cette partie qui foire), elle parcourt un certain nombre de colonnes et si une des colonnes parcourues contient autre chose que 0 alors elle colore en rouge la première cellule de la colonne en question (les cellules dans lesquelles se trouvent item x)

3) Elle fait de la cellule AV1 la cellule active -> OK !

Le 2) ne fonctionne pas MAIS, après avoir exécuté la macro, lorsque je me place en S1 par exemple (1ere cellule d'une des colonnes parcourues), la règle de mise en forme conditionnelle est bien présente. Il semblerait juste qu'elle ne soit pas exécutée et je ne comprends pas pourquoi.

Je vous joins le code de la macro ci dessous (qui au fait est enregistrée dans "Classeur de macros personnelles" sur mon PC) ainsi que le fichier sample.zip qui contient le fichier sample.csv.

D'avance merci pour votre aide.

Ben.

Sub SCAN()
'
' SCAN Macro
'
' Touche de raccourci du clavier: Ctrl+s
'
    Range("C2").Select
    ActiveWindow.FreezePanes = True
    Range("U1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($U$2:$U$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("S1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($S$2:$S$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("K1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=""ou($K$2:$K$1000 <> 0)"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("AI1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($AI$2:$AI$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("AP1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($AP$2:$AP$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("AV1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($AV$2:$AV$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("AX1").Select
    Columns("B:B").ColumnWidth = 28.57
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($AX$2:$AX$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("BC1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($BC$2:$BC$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("BK1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($BK$2:$BK$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("BO1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OU($BO$2:$BO$1000 <> 0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("AV1").Select
End Sub
7sample.zip (1.76 Ko)
Le 09/01/2019 à 16:05

Bonjour,

ta formule MFC n'est pas correcte

pour déterminer si une colonne ne contient que des 0 utilise NB.SI

=NB.SI(U2:U1000;"<>0")=0

pour programmer cette MFC via macro tu dois mettre la formule en anglais

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(U2:U1000,""<>0"")=0"
Le 09/01/2019 à 16:11

Merci...Acide Sulfurique

Tu es en train de dire que l'enregistreur de macro ne génère pas le bon code donc ? .....

Pour ma compréhension est ce que

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

"=COUNTIF(U2:U1000,"<>0")=0"

est l'équivalent macro de

=NB.SI(U2:U1000;"<>0")=0

Ben

Le 09/01/2019 à 16:26

Bonjour,

je dis que

1) ta formule de mise en forme conditionnelle n'est pas correcte (indépendamment du fait que tu veuilles l'introduire via une macro)

2) que si tu veux introduire une formule pour une mise en forme conditionnelle, il faut mettre la formule en anglais et non en français. NB.SI se traduit par COUNTIF en anglais.

Le 09/01/2019 à 16:28

Ok j'essaye ça et je vous tiens au courant.

Merci.

Le 09/01/2019 à 18:30

Re,

lorsque j'utilise le code que tu proposes au début de ma macro (je n'ai pas tout remplacé pour tester d'abord), la macro se plante avec le message : Erreur d'exécution '5' : Argument ou appel de procédure incorrect.

Je précise que mon test initial avec OU (ou OR en anglais donc) fonctionne parfaitement lorsque je fais une MFC "manuelle". C'est seulement lorsque j'essaye de l'inclure dans une macro que ça ne fonctionne plus.

D'autre part, lorsque je saisis la formule =NB.SI(U2:U1000;"<>0")=0 pour faire un test de MFC "manuel", la formule est rejetée par XL, ce qui ne me surprend pas forcément car je crois savoir que le résultat d'une formule de MFC doit être soit True soit False, ce qui n'est pas le cas ici...de plus je ne vois pas à quoi sert le =0 à la fin de ta formule.

Merci de ton aide.

Ci dessous le début du code de ma macro avec la modif suggérée.

 Range("C2").Select
    ActiveWindow.FreezePanes = True
    Range("U1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(U2:U1000,""<>0"")=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
Le 09/01/2019 à 20:09

Bonjour,

au temps pour moi, ta formule avec OU est correcte pour autant qu'elle soit matricielle, ce qu'elle est lorsqu'on l'introduit manuellement mais apparemment pas lorsqu'on le fait via macro.

p.s.: la formule que j'ai proposée renvoie bien VRAI ou FAUX, c'est le but du =0 que j'ai ajouté.

Le 09/01/2019 à 23:45

Bonjour,

bon apparemment pas possible d'introduire une formule matricielle par VBA pour une MFC.

voici une autre formule plus simple qui donne le même résultat

=SOMME(A2:A1000)<>0

via macro

    With Range("A1")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(A2:A1000)<>0"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
    End With
Le 10/01/2019 à 09:16

Hello,

as tu essayé de tourner ce code dans ma macro avec mon fichier sample ?

Car ça ne change rien apparemment.

La formule MFC est bien visible dans la cellule à formatter mais le formatage n'a pas lieu (la cellule reste blanche et n'est pas colorée en rouge).

Edit : Apparemment il y a un pb dans le calcul de la somme dans mon tableau. La formule somme....retourne 0 même lorsque un 1 se trouve dans la colonne. Pour info le format des cellules contenant les 0 est de type standard.

Merci.

Le 10/01/2019 à 10:11

Bonjour,

je n'ouvre pas les fichiers .zip, peux-tu mettre un fichier exemple ?

pour mon info personnelle : après exécution de la macro, si tu vas dans le gestionnaire de MFC, la formule est-elle en français ?

Merci

Le 10/01/2019 à 11:31

bonjour

salut h2so4

pourquoi mettre une MFC par macro ?

crée un nouveau fichier xlsx

installe Power Query

https://www.youtube.com/watch?v=gwW2CDdvUUs

(il est inclus dans les Excel plus récents)

tu ouvres ton csv avec Power Query

et tu mets une MFC

par la suite, tu fais "actualiser"

pas de VBA, pas de formule autre que dans ta MFC fixe.

simple, fiable.

amitiés et bonne année

Le 10/01/2019 à 11:49
jmd a écrit :

bonjour

salut h2so4

pourquoi mettre une MFC par macro ?

crée un nouveau fichier xlsx

installe Power Query

https://www.youtube.com/watch?v=gwW2CDdvUUs

(il est inclus dans les Excel plus récents)

tu ouvres ton csv avec Power Query

et tu mets une MFC

par la suite, tu fais "actualiser"

pas de VBA, pas de formule autre que dans ta MFC fixe.

simple, fiable.

amitiés et bonne année

Intéressant, je ne connais pas ce soft.

Je jetterai un oeil....mais pour le challenge essayons de résoudre cette histoire de macro ...si vous êtes d'accord.

Le 10/01/2019 à 11:52

Ok H2SO4,

ci joint le sample en format CSV.

La formule est bien en anglais SUM(.... et du coup tu m'as donné une idée. Lorsque je la mets en français le calcul de la somme fonctionne (voir mon post précédent ou le calcul de la somme retournait toujours 0)...et la MFC aussi (dans le classeur) Pas testé dans la macro, je le fais tout de suite

4sample.zip (1.63 Ko)
Le 10/01/2019 à 11:58

Ok avec ce code ça fonctionne...donc tout en français 

Range("C2").Select
    ActiveWindow.FreezePanes = True
    With Range("U1")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SOMME(U2:U1000)<>0"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
    End With
    With Range("S1")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SOMME(S2:S1000)<>0"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
    End With
    Range("AV1").Select
End Sub
Le 10/01/2019 à 12:03

Bonjour,

ben j'ai encore appris quelque chose, merci !

1) pas possible de mettre une fonction matricielle dans une MFC par VBA

2) les formules MFC doivent être dans la langue de la version d'excel

Rechercher des sujets similaires à "mfc via macro cale"