[XL-2000] probleme avec offset [Résolu]

[darkvadave]

Bonsoir,
la formule suivante permet de compter les jours de présence dans un planning formé de colonnes (colonne des jours ; colonne donnant le N° du jour dans la semaine_1 à 7_ ; plus 1 colonne par personne ) en ne comptant que les jeudi (par exemple) : la fonction est utilisée en dessous de chaque colonne "personne".
BgColor est la cellule de référence couleur avec laquelle les jours de présence sont notés (par exemple vert) ;
Jsem est le N° du jour de la semaine voulu (4 si on ne veut compter que les jeudis par exemple) ;
i permet de référencer la colonne contenant le N° des jours avec offset (on doit donc entrer la valeur -8 si la colonne des N° de semaine est 8 cases à gauche de la colonne "personne" testée.

Ca fonctionne, mais on ne peut pas étirer les cellules pour calculer la même chose pour toutes les personnes ie il faut à chaque fois saisir la valeur du i.
J'ai donc essayé de faire calculer automatiquement le i dans la fonction.
Avec la fonction ComptColone donnée un peu plus loin, on peut compter l'écart entre 2 colonnes, mais je n'arrive pas à l' inclure dans ma formule.
En espérant que la présentation du pb ne soit pas trop brouillon...merci d'avance.

Code :

1
2
3
4
5
6
7
8
9
'compte cellules avec une couleur de fond en fonction du jour de la semaine
Function ColorCountFctJSem(SearchArea As Object, BgColor1 As Range, i As Integer, Jsem As Integer) As Integer
Application.Volatile True
ColorCountFctJSem = 0
MaCoul1 = BgColor1.Interior.ColorIndex
For Each cell In SearchArea
     If cell.Interior.ColorIndex = MaCoul1 And cell.Offset(0, i).Value = Jsem Then ColorCountFctJSem = ColorCountFctJSem + 1
Next cell
End Function

Code :

1
2
3
4
5
6
7
8
Function comptcolone(NJour As Range) As Integer
comtcolone = 0
Dim i As Integer
Dim j As Integer
i = NJour.Column
j = ActiveCell.Column
comptcolone = i - j
End Function

[bbil]

bonsoir,
remplace ton i par un paramètre range, et passe en paramètre une cellule de ta colonne ..

puis récupére le numéro de colonne par la propriété column...

[darkvadave]

Bonsoir,
merci pour la réponse mais j'avais déjà essayé pas mal de solutions de ce style, sans succès : pour faciliter les choses, j'ai fais un classeur simplifié et restreint d'une partie de mon planning en incorporant dans un module les quelques fonctions utiles pour ce pb, la dernière étant une des solutions que j'ai essayé (à chaque fois, j'ai un résultat #valeur!).
Merci d'avance de me dire où ça coince.

[darkvadave]

Re bonsoir,
merci bbil, je n'avais pas saisi ce que tu m'avais écrit : il faut directement remplacer le i par la formule, sans essayer de créer un calcul intermédiaire.

Le problème, c'est que la référence à la cellule active induit qu'on a un calcul qui se modifie dès qu'on clique sur une autre cellule (ça, je ne m'en étais pas encore rendu compte parce que j'essayais des formules les une sous les autres, sans changer de colonne!)
Par ailleurs, si quequ'un peu me dire pouquoi la 1ère formule ne fonctionne pas, je suis preneur.

Code :

1
2
3
4
5
6
7
8
9
10
11
'Formule qui ne fonctionne pas
Function ColorCountFctJSemAuto(SearchArea As Object, BgColor1 As Range, NJour As Range, Jsem As Integer) As Integer
Application.Volatile True
Dim i as integer
ColorCountFctJSemAuto = 0
MaCoul1 = BgColor1.Interior.ColorIndex
i= NJour.Column - ActiveCell.Column
For Each cell In SearchArea
     If cell.Interior.ColorIndex = MaCoul1 And cell.Offset(0, i).Value = Jsem Then ColorCountFctJSemAuto = ColorCountFctJSemAuto + 1
Next cell
End Function

Code :

1
2
3
4
5
6
7
8
9
'compte cellules avec une couleur de fond en fonction du jour de la semaine : cette formule fonctionne
Function ColorCountFctJSemAuto(SearchArea As Object, BgColor1 As Range, NJour As Range, Jsem As Integer) As Integer
Application.Volatile True
ColorCountFctJSemAuto = 0
MaCoul1 = BgColor1.Interior.ColorIndex
For Each cell In SearchArea
     If cell.Interior.ColorIndex = MaCoul1 And cell.Offset(0, (NJour.Column - ActiveCell.Column)).Value = Jsem Then ColorCountFctJSemAuto = ColorCountFctJSemAuto + 1
Next cell
End Function

[darkvadave]

RE
Pour palier au pb de activecell, on peut ajouter une variable range qui permettra de choisir la cellule dans laquelle on rentre la fonction, mais ça fait une variable de plus à traiter : y a-t-il plus simple (ie sans ajouter de variable) pour faire fonctionner le tout ?

Code :

1
2
3
4
5
6
7
8
9
'compte cellules avec une couleur de fond en fonction du jour de la semaine
Function ColorCountFctJSemAuto(SearchArea As Object, BgColor1 As Range, NJour As Range, CaseFct As Range, Jsem As Integer) As Integer
Application.Volatile True
ColorCountFctJSemAuto = 0
MaCoul1 = BgColor1.Interior.ColorIndex
For Each Cell In SearchArea
     If Cell.Interior.ColorIndex = MaCoul1 And Cell.Offset(0, (NJour.Column - CaseFct.Column)).Value = Jsem Then ColorCountFctJSemAuto = ColorCountFctJSemAuto + 1
Next Cell
End Function

[darkvadave]

Solution donnée par Qwazerty (sur question reformulée "pb avec activecell") :

Code :

1
2
3
4
5
6
7
8
9
10
11
Function ColorCountFctJSemAuto(SearchArea As Range, BgColor1 As Range, NJour As Range, Jsem As Integer) As Integer
Dim TheCell As Range, Cell As Range
Dim MaCoul1 As Long
Set TheCell = Application.Caller
Application.Volatile True
ColorCountFctJSemAuto = 0
MaCoul1 = BgColor1.Interior.ColorIndex
For Each Cell In SearchArea
     If Cell.Interior.ColorIndex = MaCoul1 And Cell.Offset(0, (NJour.Column - TheCell.Column)).Value = Jsem Then ColorCountFctJSemAuto = ColorCountFctJSemAuto + 1
Next Cell
End Function