Boucle avec fonction Countif

Le 04/06/2014 à 09:04

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
Le 04/06/2014 à 13:17

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

Le 04/06/2014 à 13:44

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

Le 04/06/2014 à 16:39

Re,

Quid de ma deuxième question ?

Crdlt

Le 04/06/2014 à 22:20

Oui, il faut conserver la formule si dans chaque colonnes puisque le critère et la plage change

Le 06/06/2014 à 13:08

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

Le 26/06/2014 à 10:26

Merci de ta réponse Dan mais j'ai su résoudre mon problème !

Rechercher des sujets similaires à "boucle fonction countif"