Probleme : Boucle avec fonction Countif

Le 08/05/2013 à 23:40

Bonjour à toutes et à tous.

Etant novice en vba je me sers beaucoup de l'enregistreur de macro mais pour mon problème, il n'a pas l'air de m'être utile.

Sur ma feuille " Planning " j'ai des collaborateurs et des critères (cp,rtt,formation,etc...)

(-->informations que je récupere dans la feuille "structure" qui contient mon nombre de salarié et mes critères)

Avec la fonction countif je fais les totaux et je les reportes dans un onglet synthese.

Cependant, mon nombre de collaborateur (les colonnes) et de critère (les lignes) peut varier à chaque " initialisation du fichier "

(mon fichier sera distribué à plusieurs manager qui n'auront pas le même nombre de collaborateur à gerer)

Donc mon problème est que je n'arrive pas à créer une boucle afin de pouvoir faire mes totaux si mon nombre de ligne et colonne change.

La macro que j'ai faite manuellement avec l'enregistreur donne ça mais elle à un nombre de critère fixe.

Sub test()
Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R2C1)"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R3C1)"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R4C1)"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R5C1)"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R6C1)"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R7C1)"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R8C1)"
    Range("B1:B8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("B2:B8").Select
    Selection.AutoFill Destination:=Range("B2:J8"), Type:=xlFillDefault

End Sub

Pensez-vous qu'il est possible de créer une boucle tout en sachant que le nombre de salarié et le nombre de critère est connus ?

(ils sont comptabilisés et identifiés dans la feuille "structure" )

Je viens de passer 6h dessus et je capitule...

Par avance merci.

Bonne soirée à vous.

81classeur1.zip (19.57 Ko)
Le 09/05/2013 à 00:51

Bonjour,

Essayez ceci, ça devrait fonctionner peut importe le nombre de collaborateurs ou de critères :

Sub toto()
   Dim c As Range, rg As Range

   Set rg = Sheets("Synthese").Range("A1").CurrentRegion.Offset(1, 1)
   Set rg = rg.Resize(rg.Rows.Count - 1, rg.Columns.Count - 1)

   For Each c In rg
      c.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R" & c.Row & "C1)"

   Next c
End Sub

PS : Éviter les .Select dans le code. L'enregistreur de macro aime bien les ajouter mais c'est inutile dans 99% des cas et ça ralenti le code inutilement.

A+

Le 09/05/2013 à 09:48

Bonjour le Grand Chaman d'Excel

Merci pour la résolution de mon problème en si peu de temps et en si peu de lignes.

Et merci pour cette boucle car elle m'apprend également d'autres fonctions (resize, rg.count.rows...)

Par contre si vous en avez le temps, une petite explication de " pourquoi ça marche " serait un plus.

J'imagine que C et R veulent dire Columns et Rows mais le reste du code (hormis c.rows) reste incompris pour moi.

c.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R" & c.Row & "C1)"

Par avance merci.

Manouk

Le 09/05/2013 à 10:01

Parev,

la question a déjà été résolue et la méthode de Grand Chaman est d'une redoutable efficacité, mais je propose tout de même cette solution car elle bâtit tout le tableau de la feuille de synthèse à partir d'une page blanche, quels que soient le nombre de collaborateurs et de critères.

J'en profite pour donner quelques explications sur la méthode utilisée par Grand Chaman (et surement préférable à la mienne car elle a le mérite d'être simple et courte)

set rg= Sheets("Synthese").Range("A1").CurrentRegion'CurrentRegion définit le bloc de cellules autour de la cellule sélectionnée
rg.Select
Set rg = Sheets("Synthese").Range("A1").CurrentRegion.Offset(1, 1)
rg.select
   Set rg = rg.Resize(rg.Rows.Count - 1, rg.Columns.Count - 1)
rg.Select

cette partie définit la plage de calcul de la macro... pour t'en convaincre, j'ai rajouté quelques lignes de code qui décomposent les étapes de cette définition... à exécuter en pas à pas.

   For Each c In rg
      c.FormulaR1C1 = "=COUNTIF(Planning!C[1],Synthese!R" & c.Row & "C1)"

   Next c

ça, c'est la formule magique.

tout d'abord, For each c in rg : rg est défini comme étant une plage de cellules donc c représente chaque cellule de cette plage : traduit en français, cette boucle signifie : pour chaque cellule de la plage rg appliquer :

c.FormulaR1C1 = : cette partie signifie que la cellule c attend une formule (avec cette méthode, la macro écrit une formule dans chaque cellule, tu peux le vérifier en posant le curseur sur l'une des cellules de la plage) .

"=COUNTIF(Planning!C[1],Synthese!R" & c.Row & "C1)" : dans Excel, chaque formule commence par le signe égal

COUNTIF est l'équivalent anglais de NB.SI à (noter qu'en VBA, les formules sont à écrire en anglais, les ; deviennent des virgules).

Planning!C[1] sur la feuille Planning, sélection de la colonne décalée de 1 vers la droite par rapport à la colonne de la cellule sélectionnée; autrement dit, si j'écris ma formule dans une cellule de la colonne B dans synthèse, alors la colonne sélectionnée sera C dans planning... ensuite C/synthèse-D/planning ect...

Synthese!R" & c.Row & "C1 dans la feuille synthèse R=le numéro de ligne de la cellule en traitement et toujours la colonne1

donc si ta boucle traite la cellule C2 cette partie de la formule fait référence à la cellule de la colonne 1 ligne 2 donc il s'agit à chaque fois du critère.

voilà, en espérant que cela t'aide à comprendre les choses.

Cordialement,

Game Over/apprenti Chaman Excel

59manouk.zip (25.76 Ko)
Le 09/05/2013 à 13:52

Bonjour Game Over

Vos explications sont assez claires et je pense avoir bien compris.

Je confondais C[1] non pas comme indicateur de position "Colonne + 1" mais comme repère (colonne C).

J'ai intégré votre macro dans mon fichier à la suite des autres mais, paradoxalement, dans mon fichier cela me met une erreur qui n'apparait pas dans le votre....

Dans mon fichier, sur la feuille structure, quand je clique sur initialiser la procédure se déroule normalement jusqu'à une erreur de type de 13 "imconpatibilité de type".

With Sheets("Synthese")

.Select

---> .Range("A2").Resize(Dico1.Count, 1) = Application.Transpose(Dico1.Keys)

Set myPlage = .Range("B2").Resize(Dico1.Count, a - 3)

A quoi est dure cette incompatibilité d'un fichier à un autre ?

Par avance merci.

35planning-amp-capacite.zip (50.22 Ko)
Le 09/05/2013 à 14:50

Bonjour,

J'ai cliqué sur le bouton "Initialiser" pour lancer la macro et comme vous dites ça arrête à la ligne que vous avez indiqué.

Je crois que le problème vient du fait que votre Dico1 ne contient aucun élément donc "Dico1.Count = 0"

.Range("A2").Resize(Dico1.Count, 1) = Application.Transpose(Dico1.Keys)

Lorsque vous remplissez votre Dico1 un peu avant avec " For Each c In myRange", la plage "myRange" ne contient aucune donnée.

À vérifier...

Le 09/05/2013 à 15:06

C'est exact, la macro plante parce qu'au moment où elle est lancée, il n'existe pas de critère lui permettant de réaliser ce pour quoi elle a été écrite.

Tu devras dissocier la macro de la phase d'initialisation.

Le 09/05/2013 à 15:56

Je vais construire une " version 2 " en pas à pas à partir du classeur où la macro fonctionne.

Merci pour l'aide.

Manouk

Le 09/05/2013 à 16:00

Tu peux sans doute conserver cette version, mais tu dois supprimer le "call test " de ton code et lancer la macro test lorsque ton document contient déjà les critères que tu souhaites voir être calculés dans ta feuille synthèse.

Le 09/05/2013 à 18:16

J'ai crée un critère NP (Non planifié) mis par défaut dans toutes les cellules ainsi votre macro sera active à l'initialisation.

L'étape suivante est de reussir à detecter les jours (l,m,m,j,v) et les auto-planifier en hn avec un bouton.

(Je viens de comprendre comment fonctionne la fonction weekdays donc ça ira assez vite)

Merci pour votre aide et se fil de discussion.

Rechercher des sujets similaires à "probleme boucle fonction countif"