Boucle avec fonction Countif
Bonjour tout le monde,
Je suis novice en VBA, et j'aurai besoin de votre aide pour faire une boucle, dans la fonction COUNTIF que j'utilise.
Je me sers beaucoup de l'enregistreur de macro, et c'est pour cette raison je n'arrive pas à m'en sortir avec la boucle dans la macro que je veux faire.
Ma macro prend la fonction COUNTIF avec une plage précise et un critère précis qui se fait en fonction du numéro "9800,9828.."
et en fonction de ce numéro la plage varie donc elle doit se décaler de deux colonnes en plus, sachant que le critère est différent dans chaque case. Comment pourrais je faire ma boucle pour que la plage se décale de deux colonnes ?
Merci de votre aide d'avance !
Sub Arrivee()
Range("AB446").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-25]:R[1]C[-25],""=<-5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-23]:R[1]C[-23],""=<-5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-21]:R[1]C[-21],""=<-5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-19]:R[1]C[-19],""=<-5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-17]:R[1]C[-17],""=<-5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-15]:R[1]C[-15],""=<-5"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-28]C[-25]:R[1]C[-25],""=-4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-5]C[-24]:R[26]C[-25],""=-4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-5]C[-22]:R[24]C[-22],""=-4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-5]C[-20]:R[24]C[-20],""=-4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-5]C[-18]:R[24]C[-18],""=-4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-5]C[-16]:R[24]C[-16],""=-4"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-26]:R[25]C[-26],""=-3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-24]:R[25]C[-24],""=-3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-22]:R[24]C[-22],""=-3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-20]:R[24]C[-20],""=-3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-18]:R[24]C[-18],""=-3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[-16]:R[24]C[-16],""=-3"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-26]:R[25]C[-26],""=-2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-24]:R[25]C[-24],""=-2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-22]:R[24]C[-22],""=-2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-20]:R[24]C[-20],""=-2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-18]:R[24]C[-18],""=-2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-16]:R[24]C[-16],""=-2"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-26]:R[25]C[-26],""=-1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-24]:R[25]C[-24],""=-1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-22]:R[24]C[-22],""=-1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-20]:R[24]C[-20],""=-1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-18]:R[24]C[-18],""=-1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C[-16]:R[24]C[-16],""=-1"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-26]:R[25]C[-26],""=0"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-24]:R[25]C[-24],""=0"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-22]:R[24]C[-22],""=0"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-20]:R[24]C[-20],""=0"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-18]:R[24]C[-18],""=0"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-16]:R[24]C[-16],""=0"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-26]:R[25]C[-26],""=1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-24]:R[25]C[-24],""=1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-22]:R[24]C[-22],""=1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-20]:R[24]C[-20],""=1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-18]:R[24]C[-18],""=1"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-10]C[-16]:R[24]C[-16],""=1"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-26]:R[25]C[-26],""=2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-24]:R[25]C[-24],""=2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-22]:R[24]C[-22],""=2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-20]:R[24]C[-20],""=2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-18]:R[24]C[-18],""=2"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-16]:R[24]C[-16],""=2"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-26]:R[25]C[-26],""=3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-24]:R[25]C[-24],""=3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-22]:R[24]C[-22],""=3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-20]:R[24]C[-20],""=3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-18]:R[24]C[-18],""=3"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-12]C[-16]:R[24]C[-16],""=3"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-26]:R[25]C[-26],""=4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-24]:R[25]C[-24],""=4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-22]:R[24]C[-22],""=4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-20]:R[24]C[-20],""=4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-18]:R[24]C[-18],""=4"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-13]C[-16]:R[24]C[-16],""=4"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-26]:R[25]C[-26],""=5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-24]:R[25]C[-24],""=5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-22]:R[24]C[-22],""=5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-20]:R[24]C[-20],""=5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-18]:R[24]C[-18],""=5"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-14]C[-16]:R[24]C[-16],""=5"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-26]:R[25]C[-26],""=6"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-24]:R[25]C[-24],""=6"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-22]:R[24]C[-22],""=6"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-20]:R[24]C[-20],""=6"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-18]:R[24]C[-18],""=6"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-15]C[-16]:R[24]C[-16],""=6"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-26]:R[25]C[-26],""=7"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-24]:R[25]C[-24],""=7"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-22]:R[24]C[-22],""=7"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-20]:R[24]C[-20],""=7"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-18]:R[24]C[-18],""=7"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-16]C[-16]:R[24]C[-16],""=7"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-26]:R[25]C[-26],""=8"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-24]:R[25]C[-24],""=8"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-22]:R[24]C[-22],""=8"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-20]:R[24]C[-20],""=8"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-18]:R[24]C[-18],""=8"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-17]C[-16]:R[24]C[-16],""=8"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-26]:R[25]C[-26],""=9"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-24]:R[25]C[-24],""=9"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-22]:R[24]C[-22],""=9"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-20]:R[24]C[-20],""=9"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-18]:R[24]C[-18],""=9"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-18]C[-16]:R[24]C[-16],""=9"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-26]:R[25]C[-26],""=10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-24]:R[25]C[-24],""=10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-22]:R[24]C[-22],""=10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-20]:R[24]C[-20],""=10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-18]:R[24]C[-18],""=10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-19]C[-16]:R[24]C[-16],""=10"")"
ActiveCell.Offset(1, -10).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-26]:R[25]C[-26],"">10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-24]:R[25]C[-24],"">10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-22]:R[24]C[-22],"">10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-20]:R[24]C[-20],"">10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-18]:R[24]C[-18],"">10"")"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-16]:R[24]C[-16],"">10"")"
End Sub
Bonjour
Dans ta macro je vois une instruction NEXT INDICE. Il manque une instruction avec INDICE en début de macro
Doit-on conserver les formules SI dans chacune des colonnes ?
A te relire
Je me suis tromper il n'y a pas de next indice, la fonction si j'en ai besoin pour chaque case pour réaliser le calcul, et j'aimerai le faciliter avec une boucle
Re,
Quid de ma deuxième question ?
Crdlt
Oui, il faut conserver la formule si dans chaque colonnes puisque le critère et la plage change
re
Petite question au sujet de la partie du code relative au critère -4. Je constate des variations de plage.
Merci de confirmer que cette partie est bien correcte
Merci de ta réponse Dan mais j'ai su résoudre mon problème !