VBA Boucle colorer telle cellule si celle-ci est vide

Bonjour à tous,

j'ai besoin de votre aide sur une macro d'une simplicité effarante, mais pour ma défense, j'ai commencé VBA il y a quelques jours

J'aimerais simplement colorer un cellule en rouge quand deux autres de la même ligne sont vides et q'uencore une autre est non vide. Ce sera plus parlant avec mon code faux:

Sub LignesVidesRouge()

Dim WB_Classeur As Workbook

Dim S_Liste As Worksheet

Dim Nb As Integer

Set WB_Classeur = ActiveWorkbook

Set S_Liste = WB_Classeur.Worksheets("Liste complète")

Nb = TrouveLignes()

MsgBox (Nb & "lignes rouges")

End Sub

Function TrouveLignes()

i = 5

For Each Cells(i, F) In Range("5:400")

If Cells(i, M) = "" And Cells(i, N) = "" And Cells(i, B) <> "" Then

Cells(i, F).Font.ColorIndex = 3

i = i + 1

Else: i = i + 1

End If

Next Cells(i, F)

End Function

Je pense que vous avez compris où je souhaitais en venir, mais là je n'y parviens pas. L'erreur qui s'affiche est "variable requise". Alors je me doute que c'est enfantin mais je suis un enfant en VBA !

Avant cette erreur j'en avais d'ailleurs une du type 1004 au niveau de la ligne:

" If Cells(i, M) = "" And Cells(i, N) = "" And Cells(i, B) <> "" Then "

Si quelqu'un peut m'aider, je le remercie !

Bonne soirée,

SkillzZ

Bonsoir,

Tu débutes

Bon quand tu utilises RANGE: ex: RANGE("A1:D1") pour la plage de A1 à D1

si tu utilises CELLS : cela donnera pour la cellules B5 ---> cells(5,2) soit ligne 5 et colonne 2

toi tu utilises F M N B et Excel hurle pour te demander une variable, NORMAL, elle ne sont déjà pas déclarées

mais si tu remplace F par 6 , B par 2 , etc

sans explorer le reste, c'est déjà mieux

Option Explicit
Sub LignesVidesRouge()
Dim WB_Classeur As Workbook
Dim S_Liste As Worksheet
Dim Nb As Integer
  Set WB_Classeur = ActiveWorkbook
  Set S_Liste = WB_Classeur.Worksheets("feuil1") 'A remplacer par le nom de ta feuille
    Nb = TrouveLignes()
    MsgBox (Nb & "lignes rouges")
End Sub

Function TrouveLignes()
Dim i As Integer
  For i = 5 To 400
    If Cells(i, 13) = "" And Cells(i, 14) = "" And Cells(i, 2) <> "" Then
      Cells(i, 6).Font.ColorIndex = 3
    End If
  Next i
End Function

Bonjour,

Salut M12 !

j'ai commencé VBA il y a quelques jours

Rien à dire dans ce cas ! Par contre, à 33 messages tu aurais déjà eu le temps d'apprendre à te servir des balises Code pour citer du code dans ton post...

Mais revenons à l'essentiel... un examen un peu approfondi de ton code pourrait te permettre de progresser plus rapidement :

Tu déclares d'abord 2 variables, classeur et feuille... Je ne pense pas que tu opères avec plusieurs classeurs, et au cas particulier, au moment où tu lances la macro le classeur actif est nécessairement celui à partir duquel tu opères.

Or VBA te fournit une propriété de l'objet Application, ThisWorkbook qui partout dans le code te permettra de cibler le classeur contenant la macro qui s'exécute. C'est pas chouette ! tu peux l'utiliser comme une variable et ça t'évite d'en utiliser une, alors pourquoi s'en priver !

Mais voyons plus loin, tu te sers de la variable classeur pour intialiser la variable feuille. Là on n'a pas besoin de variable classeur, mais seulement de la variable feuille puisque c'est le but...

Mais voyons encore un peu plus loin : ce que tu vas utiliser c'est une plage, que tu vas définir indépendamment dans la fonction que tu appelleras. Il serait alors peut-être plus judicieux d'envoyer la plage à tester à la fonction, et d'initialiser au départ une variable plage à cette fin.

Si on déclare ta variable S_Liste en type Range, on l'initialise comme plage F5:F400 de la feuille "Liste complète" du classeur, tu peux appeler ta fonction en lui passant la plage en argument. Tu n'auras pas besoin de la définir dans la fonction (et la fonction pourra peut-être avoir d'autres utilisations...).

Tu entends dans ta proc. Sub récupérer dans une variable une valeur renvoyée par la proc. Function. Bonne idée en soi mais cela suppose que tu fasses renvoyer cette valeur par la Function, ce qui n'est pas le cas. Dans la fonction, il faut donc que tu comptes le nombre de lignes impactées pour faire renvoyer ce nombre...

D'autre part, tu fais faire l'opération de coloriage à la fonction. Bonne idée encore, une Function peut faire ce que fait une Sub et en plus peut renvoyer un résultat.

Là il y a quelques cafouillages dans l'utilisation des boucle et d'autres éléments, je passe sur les questions déjà soulevées par M12...

Une boucle For... Next s'utilise avec une variable compteur (numérique) que l'on incrémente pour parcourir à peu près n'importe quoi, d'un point à un autre, dès lors que la valeur du compteur peut permettre de désigner à chaque fois l'un des éléments que l'on souhaite examiner (on peut aussi faire la même opération un nombre de fois prédéfini, cette boucle s'y prête).

Une boucle For Each... Next s'utilise elle avec une variable objet, elle est destinée à parcourir les objets d'une collection.

Il ne faut pas les confondre et savoir ce que l'on utilise et pourquoi.

(NB- elle peut également servir à parcourir les éléments d'un tableau, dans ce cas la variable doit obligatoirement être de type Variant, ceci pour avoir une idée claire des diverses utilisation).

Incrémenter une variable à l'intérieur de la boucle s'utilise surtout avec des boucles Do... Loop pour lesquelles on définit en principe une condition de sortie mais la variation de ce qu'on fait à l'intérieur repose sur la façon dont on opère, le plus souvent donc par incrémentation d'une variable.

Rien n'interdit d'incrémenter une variable dans une boucle For... Next, mais il faut le faire à bon escient et que cela réponde à un besoin, car le parcours principal de la boucle est déjà défini dans son initialisation. Par exemple, selon un condition, si elle est vérifiée, on opère une inscription ailleurs, et on incrémente une variable qui indiquera sur quelle ligne inscrire. Cela est indépendant de la valeur de la variable boucle et ne l'impacte pas.

(Laissons de côté pour l'instant de côté les usages qu'on peut en faire pour apporter un correctif à la variable compteur, à la suite de l'examen au moyen d'un boucle interne à la première d'une partie des éléments parcourus présentant une caractéristique commune... c'estt un peu plus délicat et il ne faut pas se planter dans la mise au point.)

Dans ton cas, tu as bien une condition a tester pour savoir si tu colories ou non, et tu as besoin d'une variable pour compter le nombre de fois où tu colories, nombre que la fonction doit renvoyer.

Le renvoi d'une valeur par une fonction se fait par : NomFonction = Valeur...

Résumons-nous : la fonction parcourt une plage constituée par une colonne (donc besoin d'une variable Range pour la parcourir), elle teste la condition, si vraie elle colories la cellule et comptabilise l'action en incrémentant une variable, si non elle se poursuit...

Et à la fin elle renvoie le nombre que tu afficheras dans un message.

Quelques éléments complémentaires pour aller plus au fond.

Le coloriage : ColorIndex définit une couleur à partir d'une palette de 56 couleurs, jusqu'à Excel 2003 seules les couleurs de la palette pouvaient être affichées ; à partir d'Excel 2007 toutes les couleurs de l'espace de couleur RGB sont affichables, la palette existe toujours pour la compatibilité, mais il me semble qu'il convient de privilégier Color (répondant mieux aux capacités d'affichage des nouvelles versions d'Excel que ColorIndex) avec définition de la valeur RGB de la couleur. S'agissant du rouge (standard ou primaire), VBA t'offre en outre une constante de couleur dont la valeur est la valeur RGB du rouge : vbRed (il y a 8 constantes de la sorte, pour les couleurs de base), autant l'utiliser...

Les conditions : tu opères à partir de la colonne F et tu pointes à partir de là diverses autres colonnes...

Tu pourrais à partir de la plage testée renvoyer la feuille et définir ton adressage dans la feuille des cellules testées. On peut tout de même faire mieux et la méthode commune consiste à cibler les cellules testées à partir du décalage colonne avec la plage parcourue.

Si c est ta variable Range de parcours de la boucle, c.Offset(, 7) pointera la cellule en M de la ligne, c.Offset(, 8) la cellule en N et C.Offset(, -4) la cellule en B.

Mais je te propose une autre façon de faire pour découvrir les multiples possibilités d'adressage... un adressage relatif des cellules testées à partir de c, avec la méthode Cells : il convient de bien avoir à l'esprit que c.Cells(1, 1) n'est autre que c ! A partir de là en faisant varier les valeurs ligne et colonne de Cells (sans omettre la valeur 0) on peut pointer n'importe qu'elle cellule de la feuille : c.Cells(1, 8) pointera la colonne M de la ligne, c.Cells(1, 9) la colonne N et c.Cells(1, -3) la colonne B.

Dernier point : dans un module on place toujours les déclarations de niveau module en tête du module, suivies des procédures Function, suivies des procédures Sub, dans cet ordre. C'est une convention mais qui a aussi pour fondement de rendre l'accès aux fonctions plus rapide. Il est conseillé de repecter cette règle.

A partir de cet ensemble de considérations, on peut réécrire ainsi ton code :

Function TrouveLignes(plg As Range)
    Dim i%, c As Range
    For Each c In plg
        If c.Cells(1, 8) = "" And c.Cells(1, 9) = "" And c.Cells(1, -3) <> "" Then
            c.Font.Color = vbRed
            i = i + 1
        End If
    Next c
    TrouveLignes = i
End Function

Sub LignesVidesRouge()
    Dim S_Liste As Range, Nb As Integer
    Set S_Liste = ThisWorkbook.Worksheets("Liste complète").Range("F5:F400")
    Nb = TrouveLignes(S_Liste)
    MsgBox (Nb & " lignes rouges")
End Sub

Cordialement.

Bonjour ML12, Bonjour MFerrand !

Croyez le ou non, oui je débute en VBA. Le "code" que je vous ai soumis hier est le tout premier que j'écris, suite à quelques pages d"Open Classroom lues. Les deux autres sujets que j'ai fait à propos de VBA, concernaient des codes que j'avais récupéré de mes supérieurs et sur lesquels je devais travailler ! Pour l'ensemble des postes restants, c'était du excel pur, jamais de VBA

Merci beaucoup à vous deux, pour les explications fournies que j'ai lu attentivement et que je souhaiterais garder ! Est-il possible de retrouver facilement ses sujets sur le site ?

Un grand merci en particulier à toi MFerrand, pour la peine que tu t'es donnée à écrire toutes ces explications, de manière structurée en plus de ça. J'apprécie beaucoup, rien ne t'y oblige. M12, je ne t'oublie pas pour autant

J'ai une question par rapport au code final que tu me proposes MFerrand, concernant le c As Range. Range n'est pas un type de variable pour commencer, donc je suis surpris. C'est une classe si je ne m'abuse. On peut définir une variable comme une classe ? Cela a-t-il pour but de ne pas réécrire "Range" à chaque fois ?

Bonne journée à vous, et à mon avis, à bientôt !

SkillzZ

Bonjour,

De mon côté, quand j'ai débuté, je me suis créer un dossier avec les classeurs contenants les codes et je m'y reporte quand j'ai besoin.

Au fur et à mesure, le codage de certain module sera de l'automatisme, mais il y a et aura toujours des bouts de code auquel je me rapporte

Bonjour SkillzZ, M12

concernant le c As Range. Range n'est pas un type de variable pour commencer, donc je suis surpris.

Ne te laisse pas enfermer dans les classes... ! les classes sont des objets, des classes d'objets. Lorsque tu déclares une variable objet, tu stipules l'appartenance de la variable à une classe. Si tu la types du type Object générique, ce sera une classe non définie, mais dans tous les cas son identification en tant que devant référer à une classe d'objets exige que tu l'initialises en utilisant l'instruction Set, laquelle opère en affectant la référence de l'objet visé à la variable, laquelle acquiert ainsi toutes les caractéristiques de l'objet auquel elle réfère. Toute action sur la variable se répercutera sur l'objet...

C'est bien ce que tu fais d'ailleurs quand tu déclares une variable de type Workbook, ou de type Worksheet... tu n'es pas surpris d'utiliser la variable à la place de la désignation de l'objet ! Et tu n'a pas l'air non plus surpris lorsque retypant une de tes variables en Range pour lui affecter la plage de recherche, cette variable soit utilisée à la place de la désignation de la plage...

Ce qui semble te surprendre, c'est l'utilisation d'une variable objet dans une boucle For Each... Next. La seul chose qui diffère c'est que tu n'affectes pas directement une référence à cette variable au moyen de Set. C'est VBA qui s'en charge en lui affectant tour à tour une référence d'objet appartenant à la collection que tu parcours. Et à chaque tour de boucle, la variable représente un object distinct, que tu utilises en tant qu'objet.

Dim wb As Workbook
For Each wb In Application.Workbooks
    If wb.Name <> ....

On est dans le même cas : tu parcours la collection des classeurs ouverts dans l'Application (Application peut être omis dans ce cas, car la collection Workbooks est celle des classeurs ouverts dans l'instance en cour de l'application). La variable wb utilisée en lieu et place de chacun des classeurs te permet de tester par exemple son nom...

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worsheets
    Select Case ws.Name
        Case....

Toujours pareil, là tu parcours la collection des feuilles du classeur qui contient la macro (tu peux te passer de ThisWorkbook ou de l'indication du classeur s'il n'y a pas risque d'ambiguïté, un seul classeur ouvert ; en cas d'omission Worksheets ne réfèrera pas à un classeur mais à Application, omission sans risque si un seul classeur ouvert il n'y a qu'une collection Worksheets présente...)

Avec :

Dim c As Range
For Each c In...

on fait toujours la même chose, on parcourt une collection, à chaque tour un élément de la collection et c représente cet élément en tant qu'objet...

Dans notre cas, on avait désigné la collection par une variable : plg représentant la plage de recherche. L'utilisation d'une variable ne change rien, par contre ce qui peut présenter une difficulté (due au caractère particulier de l'objet (classe) Range, c'est que Range est tout à la fois objet et collection. Pour être précis, il aurait fallu écrire :

Dim c As Range
For Each c In plg.Cells

En omettant d'indiquer que l'on vise la collection des cellules individuelles d 'une plage, VBA l'interprète cependant ainsi par défaut dans la plupart des cas (on pourra en trouver quelques uns où la proc. plantera si on ne précise pas DésignationPlage.Cells pour cibler explicitement une collection de cellules). La précision est toujours requise s'il ne s'agit pas de Cells :

Dim r As Range
For Each r In plg.Rows

Là ce que l'on veut parcourir est une collection des lignes appartenant à la plage visée.

Pas d'inquiétude : lorsque tu maîtrisera bien tout ça, les manipulation diverses de l'objet-collection Range auront toujours à te faire réfléchir un peu plus que d'autres...

Cordialement.