Re : Erreur de compilation avec target.row
Bonjour
voici le code avant les cellules vides ""
Zbig
Private Sub Worksheet_Change(ByVal Target As Range)
'permet de sortir de la procédure si plus d'une cellule est sélectionnée
'(sinon la suite de la macro renvoie un message d'erreur)
If Target.Count > 1 Or Target.Column <= 4 Then Exit Sub 'si le nbre de cells selectionnées >1 ou si le n° Col <=4 (a -b-c-d) alors exit
'si la cellule n'est pas entre col H et M alors exit
If Intersect(Target, Range("G:N")) Is Nothing Then Exit Sub
'sinon calculs suivants
With Cells(Target.Row, "O")
Cells(Target.Row, "O").FormulaR1C1 = "=IF(AND(COUNT(RC9:RC14)=0,COUNTA(RC9:RC14)<=3),"""",(MAX(RC9:RC14)))"
.Value = .Value: End With
'calculer la formule de moyenne en colonne P
With Cells(Target.Row, "P")
Cells(Target.Row, "P").FormulaR1C1 = "=IF(COUNTA(Rc9:rc14)<3,"""",IF(AND(COUNT(Rc9:rc14)=0,COUNTA(Rc9:rc14)>=3),0,IF(COUNT(Rc9:rc14)=0,"""",IF(COUNT(Rc9:rc14)=1,MAX(RC9:Rc14)/3,IF(COUNT(Rc9:rc14)=2,(MAX(RC9:RC14)+LARGE(RC9:RC14,2))/3,AVERAGE(MAX(RC9:RC14),LARGE(Rc9:RC14,2),LARGE(RC9:RC14,3)))))))"
.Value = .Value: End With
'calculer la formule de l'ecart en colonne Q
With Cells(Target.Row, "Q")
Cells(Target.Row, "Q").FormulaR1C1 = "=IF(rc16="""","""",ABS(Rc8-rc16))"
.Value = .Value: End With
'calculer la NOTE DE perf en colonne R
With Cells(Target.Row, "R")
Cells(Target.Row, "R").FormulaR1C1 = "=IF(RC15="""","""",IF(RC6=""f"",VLOOKUP(RC15,tabf1,3),IF(RC6=""g"",VLOOKUP(RC15,tabg1,2),"""")))"
.Value = .Value: End With
'calculer la NOTE DE moyenne en colonne s
With Cells(Target.Row, "S")
Cells(Target.Row, "S").FormulaR1C1 = "=IF(Rc16="""","""",IF(Rc6=""f"",VLOOKUP(Rc16,tabf2,3),IF(Rc6=""g"",VLOOKUP(Rc16,tabg2,2),"""")))"
.Value = .Value: End With
'calculer la NOTE D ecart au projet en colonne T
With Cells(Target.Row, "T")
Cells(Target.Row, "T").FormulaR1C1 = "=IF(OR(Rc17="""",rc19=""""),"""",VLOOKUP(Rc17,tabp,2))"
.Value = .Value: End With
'calculer la NOTE generale en colonne U
With Cells(Target.Row, "U")
Cells(Target.Row, "U").FormulaR1C1 = "=IF(OR(Rc18="""",Rc19="""",Rc20=""""),"""",Rc19+Rc18+Rc20)"
.Value = .Value: End With
End Sub