Calcul nombre cellules vides entre deux valeurs

**sigor16** · 24/04/2013, 14h38

Bonjour à tous,

J'ai déjà cherché une solution à mon problème avec les fonctions de base proposées par excel mais rien ne fonctionne. Et malgré mes "très légères connaissances" en macro je me tourne vers vous pour avoir un peu d'aide.

En pièce-jointe vous trouverez un fichier qui décrit le matériel sur lequel je travail. En gros, j'ai des lignes de chiffres avec dans certains cas des cases vides (la dernière valeur de chaque ligne étant la fin de la série). J'aimerais pour chaque ligne et pour chaque valeur un code me calculant le "nombre de cases vides + 1" qu'il y a entre cette valeur et la valeur précédente.

Je prends comme exemple la 8 ème ligne (qui se compose ainsi : 0 / vide / -1200 / -500). Le code devrait donc me dire qu'entre la valeur -1200 et 0 il y a une case vide +1, donc le résultat est de 2. Et entre -500 et -1200, il y a zéro case vide +1, donc le résultat est de 1. Comme le 0 ne comprend pas de valeur avant, le calcul commence sur la deuxième valeur de chaque ligne et s'arrête à la dernière valeur (dans ce cas -500).

Il serait très aimable également si la personne qui réussit à m'aider pouvait me donner des explications sur le code, histoire que j'en apprenne un petit peu ! ;-)

J'espère avoir été assez clair et d'avance merci à tous ceux qui se pencheront sur mon problème.

Simon

**rvtoulon** · 24/04/2013, 16h21

bonjour une idée comme une autre,
il existe dans excel une formule qui calcule le nb de cellule vide d'une plage.
donc si pour ton exemple tes données vont de A1 à D1 tu peux mettre en E1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.VIDE(A1:D1)+1

ici le résultat est bien 2, pour ton exemple.

**sigor16** · 24/04/2013, 16h27

Merci pour ta réponse rvtoulon !

J'ai déjà essayé de bidouiller quelque chose avec cette fonction mais le problème c'est que la longueur de chacune des lignes ne mon fichier n'est pas toujours régulière et que les blancs ne sont pas toujours au même endroit.

En somme, le code devrait me donner un score pour chacune des valeurs de chacune des lignes... J'espère avoir été suffisamment clair ! ;-)

**rvtoulon** · 24/04/2013, 17h29

je pense ne pas être trop loin de la solution.
Comme je ne sais pas quoi faire du résultat, tu ne dis pas ou il doit s'afficher, je l'affiche avec debug.print.

Il doit y avoir beaucoup mieux, mais d'après se que je comprend de tes explications:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
Sub compter()
Dim derniereligne As Integer, dercol As String, numdercol As Integer
Dim plage As Range, y As Integer, i As Byte
 
'met dans une variable le numéro de la dernière ligne utilisée
derniereligne = (Range("A1").SpecialCells(xlCellTypeLastCell).Row)
 
'boucle sur toute les lignes utilisées à partir de la ligne 1
For y = 1 To derniereligne
    'Récupère l'adresse de la dernière colonne de la ligne
    dercol = Cells(y, Columns.Count).End(xlToLeft).Address
 
    'récupère le numéro de la dernière colonne utilisée de la ligne
    numdercol = Cells(y, Columns.Count).End(xlToLeft).Column
 
    'définie ma plage de recherche et de calcul des cellules vides
    Set plage = Range(Cells(y, 2), Cells(y, numdercol))
 
    'donne à i la valeur 1
    i = 1
 
'boucle sur chaque cellule de la ligne à partir de la colonne B
For Each c In plage
    'si l'adresse de la cellule est égale à l'adresse de la dernière cellule de la ligne
    ' alors on sort de la boucle
    If c.Address = dercol Then Exit For
 
    'si la cellule est vide et celle de droite est pleine alors
    If c.Value = "" And c.Offset(0, 1) <> "" Then
        i = i + 1
        Debug.Print i
 
    'si la cellule est vide et celle de droite est vide alors
    ElseIf c.Value = "" And c.Offset(0, 1) = "" Then
        i = i + 1
        Debug.Print i
 
    'si la cellule est pleine et celle de droite est pleine
    ElseIf c.Value <> "" And c.Offset(0, 1) <> "" Then
        i = 1
        Debug.Print i
    End If
Next c
Next y
End Sub

adapte le à tes besoins

**sigor16** · 24/04/2013, 18h19

Encore merci,

En réalité je ne sais pas comment utiliser la fonction debug.print donc je n'arrive pas à voir si la macro me donne le résultat escompté.
Mais il est vrai que j'ai oublié de mentionner l'endroit ou je voulais que les résultats apparaissent.
L'idéal serait d'afficher le résultat les un à la suite des autres en gardant la logique du ligne par ligne et cela trente lignes plus bas.
Donc pour la ligne une le résultat devrait apparaître en ligne 31, en ligne 32 pour la ligne 2 etc...

Merci ;-)

**pyloupylou** · 24/04/2013, 19h21

bonjour

menu macro
visual basic
et ensuite menu affichage et je crois fenetre execution

**rvtoulon** · 25/04/2013, 11h26

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
Sub compter()
Dim lig As Byte, col As Long, dercol As String, numdercol As Long
Dim plage As Range, y As Byte, i As Byte
Dim verif As Boolean
 
'met dans une variable le numéro de la dernière ligne utilisée
'derniereligne = (Range("A1").SpecialCells(xlCellTypeLastCell).Row)
 
'boucle sur toute les lignes utilisées à partir de la ligne 1 jusqu'a la 30
For y = 1 To 30
    'Récupère l'adresse de la dernière colonne de la ligne
    dercol = Cells(y, Columns.Count).End(xlToLeft).Address
 
    'récupère le numéro de la dernière colonne utilisée de la ligne
    numdercol = Cells(y, Columns.Count).End(xlToLeft).Column
 
    'définie ma plage de recherche et de calcul des cellules vides à partir de la colonne B
    Set plage = Range(Cells(y, 2), Cells(y, numdercol))
 
    'donne à i la valeur 1
    i = 1
 
    'définie ma ligne de report des valeurs
    lig = y + 30
 
    'définie ma colonne de départ de report ici colonne 1 donc A
    col = 1
 
'boucle sur chaque cellule de la ligne à partir de la colonne B
For Each c In plage
    'si l'adresse de la cellule est égale à l'adresse de la dernière cellule de la ligne
    ' alors on sort de la boucle
    If c.Address = dercol Then Exit For
 
    'si la cellule est vide et celle de droite est pleine alors
    If c.Value = "" And c.Offset(0, 1) <> "" Then
 
         i = i + 1
         verif = True
 
 'si la cellule est vide et celle de droite est vide alors
    ElseIf c.Value = "" And c.Offset(0, 1) = "" Then
       i = i + 1
    End If
 
    'si ma variable verif est vrai alors
    If verif = True Then
        'j'insère 30 ligne + bas à partir de la colonne A la valeur trouvée
        Cells(lig, col).Value = i
        Debug.Print i
        'je rajoute 1 à col pour aller à la colonne suivante
        col = col + 1
        'je remet ma variable verif à false
        verif = False
    End If
 
 
    'si la cellule est pleine et celle de droite est pleine
    If c.Value <> "" And c.Offset(0, 1) <> "" Then
        i = 1
        Cells(lig, col) = i
        col = col + 1
        Debug.Print i
 
    'si la cellule est pleine et celle de droite est vide
    ElseIf c.Value <> "" And c.Offset(0, 1) = "" Then
        i = 1
        i = i + 1
        Cells(lig, col) = i
        col = col + 1
        i = 1
    End If
Next c
Next y
End Sub

Voilà il y a certainement beaucoup mieux que çà.
Vois comment tu peux l'adapter à tes besoins

**sigor16** · 26/04/2013, 15h17

Bonjour,

Je crois que l'on se rapproche mais ce n'est pas encore cela, ou du moins le résultat obtenu ne me donne pas ce que j'attends.

rvtoulon, je vois que tu fonctionnes avec des conditions. Je pourrais à la rigueur te donner celle que j’imaginerais être bonnes pour que ça fonctionne.

1) Si la cellule est vide, décaler sur l'autre cellule
2) Si la cellule est pleine et la cellule de gauche est pleine : alors = nombre de cellules vides entre ces deux cellules + 1 (donc 0 + 1 = 1)
3) Si la cellule est pleine et celle de gauche est vide : alors = nombre de cellules vides entre ces deux cellules + 1 (donc x + 1)

Est-ce plus clair ainsi ?
Car dans l'exemple que tu me donnes :

n ligne 1 à partir de A1:
0 | vide | -1200 | -500 Résultat en ligne 31: 2 | 1 |
en ligne 2 à partire de A2 :
0 | -1200 | vide | -500 Résultat en ligne 32: 2 | 2 |

La réponse pour A1 est bonne, mais pour A2 elle ne l'est pas car entre -1200 et 0 il y zéro case vide +1 (donc 1) et entre -500 et -1200 il y a une case vide +1 (donc 1 + 1 = 2).

Je donne en fichier joint le tableau des résultats que j'ai fait manuellement pour pouvoir comparer avec une éventuelle correction de la macro.

Merci d'avance pour votre aide,

Simon

**rvtoulon** · 26/04/2013, 16h56

Salut,
j'ai corrigé le code il fait ce que tu demandes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub compter()
Dim derniereligne As Integer, dercol As String, numdercol As Integer
Dim plage As Range, y As Integer, i As Byte
 
'met dans une variable le numéro de la dernière ligne utilisée
derniereligne = (Range("A1").SpecialCells(xlCellTypeLastCell).Row)
 
'boucle sur toute les lignes utilisées à partir de la ligne 1
For y = 1 To derniereligne
    'Récupère l'adresse de la dernière colonne de la ligne
    dercol = Cells(y, Columns.Count).End(xlToLeft).Address
 
    'récupère le numéro de la dernière colonne utilisée de la ligne
    numdercol = Cells(y, Columns.Count).End(xlToLeft).Column
 
    'définie ma plage de recherche et de calcul des cellules vides
    Set plage = Range(Cells(y, 1), Cells(y, numdercol))
 
    'donne à i la valeur 1
    i = 1
 
    'définie la ligne de report du résultat
    lig = y + 30
 
    'définie la colonne de report du résultat
    col = 13
'boucle sur chaque cellule de la ligne à partir de la colonne B
For Each c In plage
    'si l'adresse de la cellule est égale à l'adresse de la dernière cellule de la ligne
    ' alors on sort de la boucle
    If c.Address = dercol Then Exit For
 
    'si la cellule est vide et celle de droite est pleine alors ou la cellule est vide et celle de droite est vide alors
    If c.Value = "" And c.Offset(0, 1) <> "" Or c.Value = "" And c.Offset(0, 1) = "" Then
        i = i + 1
    End If
 
    'si i est supérieur à 1 et que la cellule de droite est pleine
    If i > 1 And c.Offset(0, 1) <> "" Then
        Cells(lig, col) = i
        col = col + 1
        i = 1
    End If
 
    'si la cellule est pleine et celle de droite est pleine
    If c.Value <> "" And c.Offset(0, 1) <> "" Then
        i = 1
        Cells(lig, col) = i
        col = col + 1
    End If
Next c
Next y
End Sub

EDIT:
J'oubliais, dans le fichier que tu as mis les cellules qui se trouvent après la dernière cellule pleine des lignes 1 à 27 ne sont pas vides. Elles semblent vides, mais il reste des espaces. Si tu ne les supprimes pas la macro ne rendra pas le résultat voulus.
j'ai fait fonctionné la macro après avoir supprimé manuellement ces espaces et le résultats est OK.
Tiens moi au courant

**papouclo** · 26/04/2013, 19h12

Bonjour,
Une possibilité d'obtenir le résultat souhaité sur le classeur exemple sans code.
Ecrire en A31 la formule matricielle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(COLONNE()>SOMMEPROD(1*($A1:$J1<>""))-1;"";INDEX(TRANSPOSE(FREQUENCE(SI($A1:$J1="";COLONNE($A1:$J1));SI($A1:$J1<>"";COLONNE($A1:$J1)))+1);COLONNE()+1))

à valider Ctrl + Maj + Entrée
A recopier vers la droite jusqu'en colonne J et vers le bas jusqu'en ligne 57.
Cordialement
Claude

**sigor16** · 26/04/2013, 19h16

Un tout out grand merci, je viens de faire quelques essais et ça me semble tourner à merveille ! En plus j'ai appris quelques nouveaux trucs donc encore merci ! ;-)

Calcul nombre cellules vides entre deux valeurs

Macros et VBA Excel

Discussions similaires

Partager

Partager