Probleme de syntaxe

**laurent27530** · 05/01/2015, 17h00

Bonjour,

Cela marche super en revanche peux tu m'expliquer la syntaxe de la ligne 28

ceci pour tenter d'optimiser ma répartition journalière et supprimer les toutes les formules

Et sachant que le nom de l'onglet se trouve en D6 quel est la déclaration que je dois faire

Code :

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

1
2
dim  date as  ?
date = range("D6").value

Merci d'avance

**casefayere** · 05/01/2015, 17h37

Bonjour,

Code :

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

    .Cells(8, x + 3) = WorksheetFunction.CountIfs(NomF.Range("A2", Dl), "<>", NomF.Range("L2", Dl(1, 12)), "")

donc la valeur de la cellule en ligne 8 et colonne x+3 compte le nombre de valeurs non vide en A et vides en L, Dl(1, 12) représentant la dernière cellule en A avec un décalage de 11 colonnes, avec offset ça s'écrirait Dl.Offset(0,11)
CountIfs remplace SOMME.SI.ENS sur Excel, il suffit de regarder l'aide

Non pas de "dim date as ? car Date est un mot réservé, tu peux choisir au hasard "dim ladate as ?", pour remplacer le "?", il faut savoir ce que tu veux faire :
1 - se référer à une date donc

Code :

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

Dim ladate as Date

2 - se référer à un texte donc

Code :

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

Dim ladate as String

ensuite

Code :

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

1
2
ladate = Worksheet(Range("D6"))
Sheets(ladate).Ce que tu veux

3 - se référer à une feuille donc

Code :

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

Dim ladate as WorkSheet

, ensuite

Code :

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

1
2
Set ladate = Worksheet(Range("D6").Value)
ladate.ce que tu veux

**laurent27530** · 06/01/2015, 17h55

bonjour,

voici mon code en revanche je bug sur le nb.si.ens

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
Sub findate()
 Dim AUJ, AUTM, FINM As Date, MM, AA As Integer
 Dim NomF As String
 Cells(7, 3) = Evaluate("=YEAR(C5)&""_""&TEXT(MONTH(C5),""00"")") 'nom de l'onglet mois
 NomF = Range("C7").Value
 AUJ = Range("C5")
 Range("D9:AH9").ClearContents
 MM = Month(AUJ)
 If MM < 12 Then
 AA = Year(AUJ)
 Else
 AA = Year(AUJ) + 1
 End If
 AUTM = CVDate("1/" & MM + 1 & "/" & AA)
 FINM = AUTM - 1
 JFINM = Day(FINM)
 
 Cells(9, 4) = AUJ
 For B = 5 To JFINM + 3
 Cells(9, B).Value = Cells(9, B - 1) + 1
 Next B
 
Dim C As Range, x As Long, y As Long, z As Long
Dim Cpte As Long
Dim Tb2013, TbMensuel
With Sheets("Réalisation Journalière")
   Range("D12:AH35") = Null 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("D9:O32") = Null"
    Tb2013 = Range("D12:AH35") 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("B9:O32") = Null"
  For x = 1 To JFINM 'le nombre de jours du mois
      TbMensuel = Sheets(NomF).Range("L2", Sheets(NomF).Range("L" & Sheets(NomF).Rows.Count).End(xlUp))
    For y = 1 To UBound(Tb2013, 1)
      For z = 1 To UBound(TbMensuel, 1)
        If Tb2013(y, 1) = TbMensuel(z, 1) Then Cpte = Cpte + 1
      Next z
      Tb2013(y, x) = Cpte
      Cpte = 0
    Next y
    Set Dl = Sheets(NomF).Range("A" & Sheets(NomF).Rows.Count).End(xlUp)
    .Cells(8, x + 3) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC:AC"), Cells(y, 8), Sheets(NomF).Range("L:L"), Cells(3, y))
  Next x
  .Range("D12").Resize(UBound(Tb2013, 1), UBound(Tb2013, 2)) = Tb2013
End With
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
 End Sub

**casefayere** · 06/01/2015, 18h17

Bonsoir,
je ne sais plus ce que tu calcules, quand tu écris

Code :

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

.Cells(8, x + 3) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC:AC"), Cells(y, 8), Sheets(NomF).Range("L:L"), Cells(3, y))

1 - Pourquoi "Range("AC:AC")" et "Range("L:L")", pourquoi ne prends-tu pas en compte la plage exacte, comme dans mon exemple
2 - juste avant cette ligne essayes, afin de vérifier

Code :

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

MsgBox  Cells(y, 8).address & " " & MsgBox Cells(3, y).address

si les "Cells" appelées font parties de la feuille "Réalisation Journalière", mettre un point devant ".Cells(3, y)" sinon si c'est la feuille "NomF", le préciser

Code :

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

WorksheetFunction.CountIfs(Sheets(NomF).Range("AC:AC"),Sheets(NomF).Cells(y, 8)....

**laurent27530** · 07/01/2015, 10h40

Bonjour,

J'ai modifier le code en revanche j'ai l'erreur 1004 sur la ligne

Code :

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

   .Cells(8, x + 3) = WorksheetFunction.CountIfs(Sheets(NomF).Range("A2", Dl), "<>", Sheets(NomF).Range("L2", Dl(1, JFINM)), "")

Je seche lamentablement

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
Sub Journalier_bis()
 Dim Dl As Range, x As Long, y As Long, z As Long
 Dim Cpte As Long, NomF As String
 Dim TbMois, TbJour
 Dim AUJ, AUTM, FINM As Date, MM, AA As Integer
 Cells(7, 3) = Evaluate("=YEAR(C5)&""_""&TEXT(MONTH(C5),""00"")") 'nom de l'onglet mois
 NomF = Range("B7")
 AUJ = Range("B5")
 Range("C9:AC9").ClearContents
 MM = Month(AUJ)
 If MM < 12 Then
 AA = Year(AUJ)
 Else
 AA = Year(AUJ) + 1
 End If
 AUTM = CVDate("1/" & MM + 1 & "/" & AA)
 FINM = AUTM - 1
 JFINM = Day(FINM)
 
 Sheets("Réalisation Journalière").Cells(9, 4) = AUJ
 For j = 5 To JFINM + 3
 Sheets("Réalisation Journalière").Cells(9, j).Value = Cells(9, j - 1) + 1
 Next j
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With Sheets("Réalisation Journalière")
  .Range("C9:AC35") = Null 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("D9:AC32") = Null"
    TbMois = .Range("B9:AC35") 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("C9:AC32") = Null"
  For x = 1 To JFINM 'ici total du nombre de jour du mois
   TbJour = Sheets(NomF).Range("L2", Sheets(NomF).Range("AC" & Sheets(NomF).Rows.Count).End(xlUp))
    For y = 1 To UBound(TbMois, 1)
      For z = 1 To UBound(TbJour, 1)
        If TbMois(y, 1) = TbJour(z, 1) Then Cpte = Cpte + 1
      Next z
      TbMois(y, x + 2) = Cpte
      Cpte = 0
    Next y
    Set Dl = Sheets(NomF).Range("A" & Sheets(NomF).Rows.Count).End(xlUp)
    .Cells(8, x + 3) = WorksheetFunction.CountIfs(Sheets(NomF).Range("A2", Dl), "<>", Sheets(NomF).Range("L2", Dl(1, JFINM)), "")
  Next x
  .Range("C12").Resize(UBound(TbMois, 1), UBound(TbMois, 2)) = TbMois
End With
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
End Sub

**casefayere** · 07/01/2015, 10h51

Bonjour,

Habitues-toi à faire des controles, soit en mode pas à pas, soit en intégrant des "Stop" et voir les valeurs en passant avec la souris sur les variables, ou encore tu ajoutes juste au dessus de la ligne concernée

Code :

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

MsgBox Sheets(NomF).Range("L2", Dl(1, JFINM)).Address

**laurent27530** · 12/01/2015, 06h53

J 'ai corrigé en mettant

Code :

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

Cells(8, x + 2) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC2", Dl(1, 29)), Cells(9, x + 2), Sheets(NomF).Range("L2", Dl(1, 12)), Cells(x + 10, 2))

maintenant il m'affiche dans toute la zone et pour chaque jour le total de chaque category il ne tiens pas compte de la variable pour le jour.

Derniere tentative que j'ai effectué sans succes toujours le meme resultat affichage du total (mensuel) de chaque category chaque jour et non le total de chaque par jour.

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
Sub Journalier_bis()
 Dim Dl As Range, x As Long, y As Long, z As Long
 Dim Cpte As Long, NomF As String
 Dim TbMois, TbJour
 Dim AUJ, AUTM, FINM As Date, MM, AA As Integer
 Cells(7, 2) = Evaluate("=YEAR(B5)&""_""&TEXT(MONTH(B5),""00"")") 'nom de l'onglet mois
 NomF = Range("B7")
 AUJ = Range("B5")
 Range("C9:AC9").ClearContents
 MM = Month(AUJ)
 If MM < 12 Then
 AA = Year(AUJ)
 Else
 AA = Year(AUJ) + 1
 End If
 AUTM = CVDate("1/" & MM + 1 & "/" & AA)
 FINM = AUTM - 1
 JFINM = Day(FINM)
 
 Sheets("Réalisation Journalière").Cells(9, 3) = AUJ
 For j = 4 To JFINM + 3
 Sheets("Réalisation Journalière").Cells(9, j).Value = Cells(9, j - 1) + 1
 Next j
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With Sheets("Réalisation Journalière")
  .Range("C11:AG35") = Null 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("D9:AC32") = Null"
    TbMois = .Range("B11:AG35") 'ici remettre toutes les colonnes _
  quand le fichier aura toutes ses feuilles donc ".Range("C9:AC32") = Null"
  For x = 1 To 31 'ici total du nombre de jour du mois
   TbJour = Sheets(NomF).Range("L2", Sheets(NomF).Range("AC" & Sheets(NomF).Rows.Count).End(xlUp))
    For y = 1 To UBound(TbMois, 1)
      For z = 1 To UBound(TbJour, 1)
        If TbMois(y, 1) = TbJour(z, 1) Then Cpte = Cpte + 1
      Next z
      TbMois(y, x + 1) = Cpte
      Cpte = 0
    Next y
    Set Dl = Sheets(NomF).Range("A" & Sheets(NomF).Rows.Count).End(xlUp)
    For L = 3 To 31
    For M = 11 To 35
    Cells(M, L) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC2", Dl(1, 29)), Cells(9, L), Sheets(NomF).Range("L2", Dl(1, 12)), Cells(M, 2))
    Next M
    Next L
 
  Next x
  .Range("B11").Resize(UBound(TbMois, 1), UBound(TbMois, 2)) = TbMois
End With
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
End Sub

Les ligne que j'ai modifie son de la ligne 43 a 47 soit

Code :

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

1
2
3
4
5
    For L = 3 To 31 'le jour  du  mois
    For M = 11 To 35 ' la category a comptabiliser
    Cells(M, L) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC2", Dl(1, 29)), Cells(9, L), Sheets(NomF).Range("L2", Dl(1, 12)), Cells(M, 2))
    Next M
    Next L

Merci pour votre aide

**Qwazerty** · 12/01/2015, 20h44

Salut

Hum, ça me semble bien compliqué :p, un petit coup de main Casefayere ?

Laurent, est ce que ça pose un problème si l’ensemble des données de tes onglets "mois", sont rassemblées dans un seul et unique onglet.
Ça peut paraitre plus "bordélique" mais le gros avantage c'est qu'en faisant ça tu ouvres la possibilité d'utilisation des Tableaux Croisés Dynamiques... je suis pas un utilisateur très aguerrie (à tord je pense) des TCD, mais dans ton cas regarde à la vitesse où tu peux avoir un résultat qui me parait très correct (voir fichier en PJ).

J'ai tenté de mettre la formule

Code :

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

=decaler($A$1:$M$1;0;0;NB.si($M:$M;"<>"))

dans une étiquette au niveau classeur (Nom de l'étiquette : Tableau_Complet) et de l'utiliser en tant que plage contenant les données du TCD, comme ça quand tu ajouteras des données à la suite du tableau, si tout se passe bien elles seront prises en compte sans rien avoir à changer, tu testeras.

Quand tu sélectionnes un TCD, dans les menus qui lui sont liés, tu trouveras plein d'option pour modifier son apparence et son contenu.

Question : Quand Category est vide qu'est ce que ça implique?

++
Qwaz

**casefayere** · 12/01/2015, 21h17

Bonsoir Stephane, Laurent, le forum

Hum, ça me semble bien compliqué :p, un petit coup de main Casefayere ?

Je suis un peu largué car, apparemment ce n'est pas le code que j'ai proposé

**laurent27530** · 13/01/2015, 15h32

bonjour,

je suis d'accord ce n'est pas ton code mais je tente de multiple test parce que j'ai besoin d'obtenir un résultat.

Code :

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

1
2
3
4
5
    For L = 3 To 31 'le jour  du  mois
    For M = 11 To 35 ' la category a comptabiliser
    Cells(M, L) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC2", Dl(1, 29)), Cells(9, L), Sheets(NomF).Range("L2", Dl(1, 12)), Cells(M, 2))
    Next M
    Next L

pour répondre a qwerty je ne peux pas faire de régroupement de mois il me faut obligatoirement un onglet par mois.

**Qwazerty** · 13/01/2015, 21h20

Salut

Alors sinon, pour le tableau de rapport annuel, dans la cellule D9 (2ème cellule de Janvier), tu places cette formule

Code :

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

=NB.SI.ENS(INDIRECT("'"&$F$4&"_"&TEXTE(COLONNE()-3;"00")&"'!L:L");$B9)

Tu l'étires sur la colonne et ensuite tu l'étires sur le tableau complet.

Puis dans la 1ère ligne du tableau (valeur vide), tu places la formule suivante et tu l'étires sur la ligne complète.

Code :

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

=NB.VIDE(DECALER(INDIRECT("'"&$F$4&"_"&TEXTE(COLONNE()-3;"00")&"'!L1");0;0;NB.SI(INDIRECT("'"&$F$4&"_"&TEXTE(COLONNE()-3;"00")&"'!M:M");"<>");1))

J'oubliais, j'ai supprimer toute les colonne vide dans les onglets 2013_xx (de sorte que category se retrouve en colonne L).
Puis en colonne M, tu étends la formule suivante en partant de la cellule M2

Code :

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

=TEXTE(B2;"j")

(c'est pour le tableau journalier mais je l'utilise pour compter le nombre de ligne pour les formule de la ligne une de la feuille annuel, donc il faut la mettre pour que tout se calcule bien)

Pour le second tableau (répartition par journée)

En cellule D11

Code :

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

=NB.SI.ENS(INDIRECT("'"&$D$6&"'!L:L");"";INDIRECT("'"&$D$6&"'!M:M");D$9)

A étendre sur la ligne complète

En cellule D12

Code :

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

=NB.SI.ENS(INDIRECT("'"&$D$6&"'!L:L");$C12;INDIRECT("'"&$D$6&"'!M:M");D$9)

A étendre sur le reste du tableau

Pense à supprimer toutes les formules qui traine à droite à gauche et qui ne te serve plus, ça allégera un peu le temps de calcul.

++
Qwaz

**laurent27530** · 14/01/2015, 07h43

Bonjour,

Dans le tableau journalier je dois pouvoir changer le mois et avoir les valeurs du mois, je ne veux pas de tableau journalier pour chaque mois.

D'où le tableau actualisé par macro.

Code :

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

Cells(M, L) = WorksheetFunction.CountIfs(Sheets(NomF).Range("AC2", Dl(1, 29)), Cells(9, L), Sheets(NomF).Range("L2", Dl(1, 12)), Cells(M, 2))

en colonne AC colonne 29 de l'onglet 2013_01, j'ai la date du jour lorsque je fais Cells(9, L) il trouve bien la date recherchée

en colonne L colonne 12 de l'onglet 2013_01, j'ai la category lorsque je fais Cells(M,2 ) il trouve bien la category recherchée

Dans mon tableau il m'affiche le total global de chaque category sur touT le mois.

**Qwazerty** · 14/01/2015, 17h48

Salut

Dans le tableau journalier je dois pouvoir changer le mois et avoir les valeurs du mois, je ne veux pas de tableau journalier pour chaque mois.

Je ne comprends pas ta remarque, tu as essayé?
Les formules tiennent compte de la cellule qui contient le mois.

++
Qwaz

**laurent27530** · 21/01/2015, 10h49

Bonjour,

j'ai modifier le code il marche, il manque optimisation.

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 Journalier_bis()
 Dim Dl As Range, x As Long, y As Long, z As Long
 Dim Cpte As Long, NomF As String
 Dim nb As String
 Dim test, tast As Variant
 Dim AUJ, AUTM, FINM As Date, MM, AA As Integer
 Cells(7, 2) = Evaluate("=YEAR(B5)&""_""&TEXT(MONTH(B5),""00"")") 'nom de l'onglet mois
 NomF = Range("B7")
 AUJ = Range("B5")
 Range("C9:AG35").ClearContents
 MM = Month(AUJ)
 If MM < 12 Then
 AA = Year(AUJ)
 AUTM = CVDate("1/" & MM + 1 & "/" & AA)
 Else
 AA = Year(AUJ) + 1
 AUTM = CVDate("1/" & MM & "/" & AA - 1)
 End If
 FINM = AUTM - 1
 JFINM = Day(FINM)
 nb = Worksheets(NomF).Range("A200000").End(xlUp).Row
 
 Sheets(NomF).Select
 
    If Worksheets(NomF).AutoFilterMode Then
       Worksheets(NomF).AutoFilterMode = False
    End If
    Range("A:AD").Select
     With Selection.Font
         .Name = "Calibri"
         .Size = 8
     End With
 
 Sheets("journee").Cells(9, 3) = AUJ
 For j = 4 To JFINM + 3
 Sheets("Journee").Cells(9, j).Value = Cells(9, j - 1) + 1
 Next j
 
 Sheets(NomF).Activate
  For a = 2 To nb 'ici total du nombre de jour du mois
  tbst = Cells(a, 29) & Cells(a, 12)
  Cells(a, 30) = tbst
  Next a
 
  Sheets("journee").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With Sheets("journee")
  For z = 1 To JFINM 'ici total du nombre de jour du mois
  tast = (Cells(9, z + 2) & Cells(11, 2))
  Cells(11, z + 2) = Application.WorksheetFunction.CountIf(Sheets(NomF).Range("AD:AD"), tast)
  Next z
  For u = JFINM + 1 To 31 'ici total du nombre de jour du mois
  Cells(9, u + 2).Value = "-"
  Next u
  For x = 1 To JFINM 'ici total du nombre de jour du mois
    For y = 12 To 35 'le nombre de category
    test = (Cells(9, x + 2) & Cells(y, 2))
    'MsgBox test
   Cells(y, x + 2) = Application.WorksheetFunction.CountIf(Sheets(NomF).Range("AD:AD"), test)
    Next y
  Next x
End With
 
Sheets(NomF).Activate
Range("AD1:AD" & nb).ClearContents
 
Sheets("Journee").Activate
 
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
End Sub

Probleme de syntaxe

Macros et VBA Excel

Discussions similaires

Partager

Partager