1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| For Each cel In Selection
'pour chaque société présente en colonne A, calculer les formules en reprenant le nom des sociétés
If Cells.Find(What:="s1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "=COUNTIF('s1'!R2C8:R1500C8,"">0"")"
End If
Next cel
For Each cel In Selection
If Cells.Find(What:="s2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate Then
ActiveCell.Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "=COUNTIF(s2!R2C8:R1500C8,"">0"")"
End If
If Cells.Find(What:="s3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "=COUNTIF('s3 '!R2C8:R1500C8,"">0"")"
End If
If Cells.Find(What:="s4", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ActiveCell.Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "=COUNTIF('s4'!R2C8:R1500C8,"">0"")"
End If
Next cel |
Partager