Formule CountIf sur une plage variable

Bonjour,

Après plus de 2h de recherches et de bidouillages en tout genre je me décide à baisser les armes et à demander de l'aide!
Je vous explique du mieux possible mon souci.
J'ai un tableau généré par macro à partir d'un autre fichier, en en-tête j'ai des noms de personnes (en nombre variable), sur la première colonne j'ai tous les jours d'un mois. Sur la dernière colonne je souhaite rajouter une formule qui va dénombrer le nombre de 1 que l'utilisateur va saisir ensuite pour les jours de présence des différentes personnes. C'est là que ça coince... J'utilise un CountIf mais je pense que ma syntaxe n'est pas bonne car aucune tentative ne fonctionne..

Voici mon code:

Sub FormuleComptage()

Dim CelEffectif As Range
Set CelEffectif = Cells.Find(What:="Effectif", lookat:=xlPart) 'recherche la cellule "Effectif" afin d'identifier la colonne où va être notée la formule.


For n = 2 To Range("A" & Rows.Count).End(xlUp)

Set Zone = Range(Range("A1").Offset(n, 1), CelEffectif.Offset(n, -1))

CelEffectif.Offset(n, 0).Formula = "=COUNTIF(Range(Zone),1)"

Next n

End Sub


J'ai aussi essayé sans nommer la plage à tester ou en déclarant la première cellule et la dernière cellule de la plage:

premCel = Range("A1").Offset(n, 1)
derCel = CelEffectif.Offset(n, -1)

Plage = (prem & ":" & der)
CelEffectif.Offset(n, 0).Formula = "=COUNTIF(Range(Plage),1)"

Mais visiblement ça ne marche pas non plus...

Je crois qu'à force d'avoir le nez dessus et d'avoir tout essayé je finis pas tout mélanger et ne plus rien voir donc si quelqu'un voulait bien m'éclairer sur la méthode à suivre je lui en serais grandement reconnaissante!

Merci beaucoup!